How to create Stock portfolio with Excel

More articles

Saurabh Gupta
नई तकनीक का आविष्कार, गैजेट्स, उपभोक्ता प्रौद्योगिकी और सॉफ्टवेयर के लिए आपका स्रोत. कंप्यूटर, स्मार्टफोन, इलेक्ट्रॉनिक गैजेट्स और इंटरनेट सामग्री पर नवीनतम रुझानों के लिए हमारी वेबसाइट देखें!

Using Excel we are able to perform calculations and generate a lot of graphs that can really provide insight to your portfolio and the market in general say for example you want to know the extent of diversification in your portfolio and using Excel we can so show you not only diversification across stocks but the different levels of composition within. The industries that you are invested in well In all honesty this depends on what kind of investor you want to be right, do you want to be more of a hands-on person or do you want to just let it do it’s own thing and I am sure you guys are familiar with this all else being equal. the theory is that the more effort you put in, the greater the rewards. I mean at the end of the day you want to know where you are putting your hand earned money? Well I hope that’s yes for you so first we will look at how to create a stock portfolio in Excel and then we will see how we can play around within the data to address the issue of diversification and last but not the least performance doesn’t really mean anything unless there is a comparison so I will show you how you can benchmark your performance against the the general market.

How to build a stock portfolio

First off for this portfolio I will use a hypothetical one of the top of my head. I will write the first 10 stocks I think of.

How to build Stock portfolio

so don’t read too much into my choices these are purely hypothetical so we will turn these tickers into the stock data type. The radially available attributes I would put are the ticker symbol which will come in handy letter on the current price industry, market cap and the P/E ratio obviously these attributes are not enough so we will need to add in our own attributes such as quantity, market value cost and the total gains/ losses. Total gains/ losses will be the overall percentage gained are lost relative to  your cost in order to produce these columns we will need to build a separate sheet called transaction history, so the sheet will have to buys and sells you have made so far and the idea is to feed this information though so the portfolio table is updated automatically. This transaction history table would include the ticker  symbol, the action whether it’s buy or sell, quantity.

Portfolio and transaction history

Price and the total amount,so again hypothetical transactions now let’s go back to the portfolio table for quantity we can now feed this from the other sheet we don’t really want to manually add the quantity because that would make the table pointless for this we can use the the SUMIF formula.

How to create Stock portfolio with Excel

basically this formula means if a certain criteria is met then we want the sum of it this formula takes three inputs. The range is the cells in the transaction history table that have the ticker symbol as this is the reference Excel uses to add up the the transaction. in this case the criteria we are using is the ticker symbol because we want to add up all of the quantities of the transactions that relate to that ticker symbol and the last input is fairly easy to understand the sum_range is the cells and the table that you wish to add up which is quantity column this table is really useful when you make a lot of transactions and you are wanting to calculate the the average cost of your position so using the SUMIF formula again we can calculate the total cost of your position.

Then you can simply take the total cost and divide that by the quantity so once we have those columns market value and total gains/losses are just a matter of using formulas so market value would equal the current price times the quantity and the total gain/losses would be dividing the difference between the market value and total cost by the total cost.

Portfolio diversification

Before we can start analysing diversification we need to add another column which calculate the percentage of each stocks in the portfolio. This is simply the value of the stock divided by the total value of the portfolio, so we have got a fair bit of data now let’s go ahead and make some charts so we will go and select the relevant columns. In this case we will select the column with the ticker symbols and also the percentage of portfolio columns, so if you know what type of chart you should use you can head over to the insert tab, in the ribbon and click on recommended charts where Excel will give you some suggestions based on your data go through the list and see what type of chat you like, aim is to pick a chat that tells the story. In this case you can see that the pie chart is probably the best but you never know some people might like bar charts but personally I would go for the pie chart the default style of the chart is a bit hard to read so we will change the design up a bit. we want each of the sections of the pie to be clearly labelled so we will go to add chart element under the chart design tab and click on data labels to select data call out now the chart is looking much better.

Pie chart of portfolio

if you want to chart by industry you will have to create some sort of summary tables as you will realise that Excel won’t group the same categories together if we use same method as before. The simplest way to create a summary table is to use a pivot tables. first we will select the table and navigate to the insert tab, in the ribbon and click on to the table on the very left so now you can start constructing the summary table, so we are basically recreating the table but with a more powerful sorting function for example let’s grab the industry field and percentage of portfolio field so let’s create this pie chart. you realise that if you switch the industry field with the stock field Excel will produce the same chart as before. Another function that could be useful to you is adding slicers.

There are two kind of slicers a general one and a timeline one adding slicers give you the flexibility to carve out the data you want. Using an industry slicer you are able to delver further into the specific compositions within each industry but what does this all mean you may be wondering I am sure we will know the saying that we shouldn’t put all our eggs in one basket on the face of it, it may seem very straight forward but it all depends on how you look at it right? What type of baskets are you holding? What is it made of? Are there cushions in your basket? I know all this main sound abstract what I am really trying to say is how well do you know your Investments? Simple question you can ask yourself do I know what the company does? How many revenue streams does the company have? Now that brings me to my next point do you want to perform better than the market? I will show you are ways you can benchmark your performance against the overall market using Excel.

Performance Benchmarking

So our aim is to compare our performance with the markets. Let’s just compare our year to date performance with the reliance but first you will need to export the year to date balance for your portfolio. Next we will need to download the year to date data for the reliance and navigate to historical data and make sure the date is YTD apply the filter and click download. We will be using the adjusted close price so you can just copy this columns over the same sheet with your balances since the two sets of data are are on different scales we need to standrised or rescale one set of data to other.

Comparing performance with company

here  we make the first cell the same then after that it’s just a simple exercise of multiplying the previous days balance by the percentage change. How we can plot them on a line chart to see how they fare with each other. Bear in mind that the appreciation and how your account balance may include dividend reInvestments or any deposits, withdrawals that were made during the year. if that were the case then you may need to adjust for those events to get a more accurate picture of your portfolio performance. In short this is a quiet a good way to see whether you need to make adjustments to your portfolio composition of course it will depend on how much risk you can tolerate for example can you tolerate your portfolio to decrease more than the market during bad times but be compensated for by a larger increase when there is a good times ahead?

What did you learn today

I hope you have liked my article on How to create Stock portfolio with Excel. It has always been my endeavor to provide complete information about the future of stock market to the readers, so that they do not have to search any other sites or internet in the context of that article.

This will also save their time and they will also get all the information in one place. If you have any doubts about this article or you want that there should be some improvement in it, then you can write comments below for this.