I am sure you would agree when I say tracking your dividends and portfolio performance is one of the most challenging aspects of dividend investing. Most excel formulas work for few months before having issues with data providers leaving you helpless.
What if we get the data directly from the source? IEX is an exchange that provides an Application Programming Interface(api) to get dividend data for free and reliably.
In this blog post we will make use of a google sheets, latest IEX cloud api and google finance api to create your own dividend tracking sheet.
So head over to google sheets and start a new sheet to follow along.
Just give me your version of dividend tracking spreadsheet
FYI if you do not want to go through the whole process of creating a new sheet from scratch, adding formulas, formatting it etc, you can just go to the last section and download my own google sheet. Then you can use it to do dividend tracking of your own portfolio. For other’s who want to be more adventurous, please follow along.
Stock Data sheet
Let’s first work on the Stock Data sheet, this sheet will have info like stock ticker, current price, current yield, yield on cost, quantity you own, etc. So, on the top row, let’s have these headers on a new google sheet: (I added things like Ticker, Name, Quote, Avg Cost per Share, Cost Basis, Dividend Yield, Dividend income, PE ratio, eps etc.)
Our next step is to get data populated in this sheet. in order to do so, we will make use of google finance function in sheets. You can read more about it here. Basically syntax for getting anything from google finance function is:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
So, the ticker name is mandatory and after that you can have 1 or more attributes.
- Going back to our sheet, you need to populate the column A with ticker of your stock. To populate its name, you can use =GOOGLEFINANCE(A2,”name”) under B2 shown below
- Similarly, you can use formulas like given below to get other details about the respective stock:
Function & Syntax Description =googlefinance(A2,”price”) To get the latest price =googlefinance(A2,”pe”) To get the Price earnings ratio =googlefinance(A2,”eps”) To get the earnings per share =googlefinance(A2,”high52″) To get 52 week high price =googlefinance(A2,”low52″) To get 52 week low price =googlefinance(A2,”marketcap”) To get market cap of the stock
After I fill some of these values for my first row, I get the following:
- When it comes to quantity purchased, avg cost basis per share, total cost basis, those depend from person to person and these values can easily be obtained from your brokerage account. After you get those you can very easily fill the following using formulas mentioned here:
Column in Excel Formula Market Value =C2*D2 Change $ =G2-F2 Change % =(H2/F2)*100
After that, our sheet looks something like this:
- Now coming to dividends, I have observed Google’s function is not that great. Its hit or miss. Sometimes it shows dividends sometimes it doesn’t. However, I found another api we can use to get the data related to dividend columns which is more reliable than Google’s api. Its called IEX cloud API. Up until mid June 2019 they used to allow to get the dividend data for free without any registration. But now they require you to register and get an api key.
- So, lets try to do that. Head over to the IEX Cloud registration page and create account.
- Next choose the START plan which is free. It provides 500K requests to the API every month which is more than enough for a pretty big portfolio. Even if you request to get data from it multiple times a day every day for the whole month.
- Verify your account from your email and then login to the iex cloud api account. After verification, log on and from the home page of iex cloud api account, head over to API Tokens section as shown:
- Under the API Tokens section you will find your key which you can use in google sheets. Copy the publishable token as shown using the copy button. Now here I have 2 options to get dividend amount into your sheet:
Trailing Twelve Month Dividend (Simple and Very Easy)
Here is the formula which we will use in our google sheet. We add a new column for that and use the function below to calculate for AAPL ticker: =IMPORTDATA(CONCATENATE(“https://cloud.iexapis.com/stable/stock/”,$A2,“/stats/ttmDividendRate?token=xxxx”))
The part that says xxxx needs to be replaced with your own api key you copied in last step. Once you do that, you should be able to see the ttm dividend rate. So for AAPL, this will return 2.92 (0.73*4). Current dividend is 3.08(0.77*4) . This is shown on line 1 of my google sheet if you choose to download my version.
Current Dividend (Less easy, but still easy)
In this method, you will need to copy this code and paste it into the script editor of your google sheet as shown:
Once you do this, you are mostly done. The formula to use this script in a cell to get the dividend amount is:
Again, you will need to replace the xxxx with your personalized api key from the last step. This should get you the current dividend for most US listed stocks. For e.g. for AAPL this will return 3.08 (0.77*4). This is just some code I wrote to get the most recent dividend. Then use that and the dividend frequency to get the annual dividend amount. For some stocks like BUD which pays out twice a year and has payouts where one is larger and next is smaller, this might not work. Please let me know if you come across any other stocks for which this script fails below in comments. I will try and improve the script code to account for any issues you may notice. This particular formula usage is shown in line 2 of my sheet if you download it. Additionally in case this second more improved method doesn’t work, you can always go back to using the first method shown in last step. You can absolutely use both these methods together in a sheet. Also both these methods along with script code is already added in my sheet of you choose to download that. You would not need to add it yourself and will be able to just use it.
- Do remember to replace the xxxx with your API Key, since IEX no longer allows users to share the same key to get this data.
- With the information about dividend per share, you can easily get information about columns on dividends as follows:
Column Formula Annual Div Income =D2*J2 Yield on Cost =(K2/F2) format as percent Annual Yield =(J2/C2) format as percent
- In addition to this, I have 2 columns that I added at the end, Actual Shares Purchased & Actual Cost Basis. These are completely optional and will be used to calculate your real returns on original investment. So, if you do DRIP, this will be very helpful. These columns must be filled manually, and you need to go to your brokerage website to find this info. For instance, I have some WFC stock which I bought 3-4 years back and turned DRIP on. Now last year I bought some more WFC stock out of my own pocket so my WFC holdings in my broker account look like this:So, what I know is all my DRIP transactions are anything with fractional shares in quantity column and my whole number quantity transactions are made with actual out of pocket money. Therefore, I just add up the quantity columns for whole numbers to get the Actual Shares Purchased column. I add up the cost basis for those transactions to get my entry for the Actual Cost Basis column. Important thing to remember is you must update these 2 columns any time you increase position in a stock with actual money or sell some of your positions. Those transactions will affect values in these 2 columns. When you buy any new stock, value will be easy to calculate first time. It should be same as D(quantity) and F(Cost basis). So, ideally you shouldn’t be trading much and this update will be very rare.
- And now our first row is completely done:
- Now, in order to add new stocks, your work keeps getting smaller and easier. For instance, let’s add some tickers like BLK, DIS, MO, PSX,SO,QCOM, VFC, ZBH etc.
- After adding the tickers from your portfolio, all you need to do is drag down from bottom right of the cell of first row in a column. That will basically call the same formula for that column for all your tickers. I have highlighted the columns for which you can do this. After doing it for all these columns here is what we get:
- Now enter information in columns D, E, F, P,Q and R in that order. Rest of the columns you can again drag down as show in previous screenshot. So now we are starting to get all of our data in the stockdata sheet.
- Now, lets add some totals and calculate returns and format the table to get some colors in:Notice how the return incl. dividends is a tad bit higher mostly because of some highlighted rows where I assumed, I bought some shares after receiving dividends using DRIP. Please also note these are just numbers I made up, they might not be factually correct, but this is just to give an example of how you can use the last 2 columns in the sheet. This is the end to the stockdata sheet for our dividend tracking.
Pie Chart showing your Sector allocation
Now that our stockdata sheet is ready lets do some graphs. Let’s say you want to be aware of which sectors you are allocated into and how much of your total portfolio.
- Lets add a new sheet called sectorallocation and add the sectors you want to track as shown:
- Now, under the percentage column, in order to get the correct allocation we are going to add this formula for utilities := ((SUMIF(StockData!P2:P12,”Utilities”,StockData!G2:G12))/StockData!G13)*100
- What it will do is, see the sectors we assigned in our main stockdata sheet for any of the rows say Utilities, if so add the values in those rows up and divide by the total value to get the percentage allocated to that sector. Similarly, do this for all the other rows. You can copy the formula, but you will need to replace the Utilities with the sector of the row. So, we get this:
- Now, lets add a pie chart to the sheet and add your data ranges from A2:A11,B2:B11. After that, we get the following:
- At the end you get a pretty nice pie chart showing your allocation in different sectors. This can help you be aware in what sectors you are more exposed and if you decide to buy a new stock, you can see what sector you are very less exposed to and maybe look at a company there.
Monthly dividend tracking sheet
So far, we created our stockdata sheet which shows our yield on cost, annual dividends, return with and without dividends etc. Our sectorallocation sheet shows how much or less are you exposed to various sectors. In addition to these, let’s create a sheet to track our monthly dividends. This sheet will help us to create more data and understand our monthly cash flows from dividends. It can also eventually allow you to plot your dividend gains over few years/months of investing and show you the compounding effect which everyone talks about in dividend investing.
- To do this, add a new sheet and enter the columns as shown till December:Now for the first 2 columns, get the data from your StockData sheets and populate them. Once that’s done, now you need to manually fill in what dividends you got every month from different stocks. This process can be a bit time consuming. Although, you should be able to get all the data from your broker. On my broker’s website, I just login and go to the transactions screen. Then filter to only show dividend transactions. This allows me to go through a few months of transactions at a time and fill this sheet quickly. In addition, we can also enter information for 2018 and previous years if possible, in new sheets. However, this depends on if you can get the data from your broker for previous years. Then using that data, we can add some YOY(Year Over Year) percentage increases for your dividend stocks as shown:
- Now this give you better idea of how fast your dividend is growing every month. You can even play around with some extra rows and add Year end increase in dividends year over year for the full total etc. Now lets create a graph to show dividends received form companies in Q1 2019. Lets add a bar chart to the sheet for 2019 Monthly dividends and use the following data ranges:
- Again you can play around with the data ranges to change this chart from quarterly to annual ranges. That way you can get different type of graphs.
OK this is great! Now give me this sheet!
I do realize making your own excel from scratch can be daunting even with the above given steps. So in case you just want a copy of the sample google sheet. Please enter your email below and I will send you the sheet with easy to follow instructions on how you can set it up for your portfolio. Again, I think if you are buying individual stocks for dividends, its utmost important to track and measure your performance. So you know how you perform against the market and can see the effects of compounding in real time. I usually update my personal sheet once a quarter and publish my quarterly results.