How to Add Read More in Divi

I am sure you would concur 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 bug with data providers leaving you helpless.

What if we get the data directly from the source? IEX is an exchange that provides an Awarding Programming Interface(api) to go dividend data for free and reliably.

In this web log post nosotros volition brand utilize of a google sheets, latest IEX deject api and google finance api to create your own dividend tracking sheet.

So head over to google sheets and showtime a new sheet to follow forth.

Just give me your version of dividend tracking spreadsheet

FYI if you practise not want to go through the whole process of creating a new canvas from scratch, adding formulas, formatting information technology etc,  y'all can just become to the last section and download my own google canvas. Then you can use it to do dividend tracking of your ain portfolio. For other's who want to exist more audacious, please follow along.

Stock Information sail

Let's first work on the Stock Data sheet, this sail will accept info like stock ticker, electric current toll, current yield, yield on cost, quantity y'all own, etc. So, on the top row, let's take these headers on a new google canvass: (I added things like Ticker, Name, Quote, Avg Cost per Share, Cost Footing, Dividend Yield, Dividend income, PE ratio, eps etc.)

Our next pace is to become data populated in this sheet. in order to practice so, we will make use of google finance function in sheets. Basically syntax for getting annihilation from google finance office  is:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

And so, the ticker name is mandatory and afterward that you can have i 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 lot can use =GOOGLEFINANCE(A2,"proper name") nether B2 shown beneath
• Similarly, you can use formulas similar given below to go other details nigh the corresponding stock:
  

  Function & Syntax	Description
  =googlefinance(A2,"toll")	To go the latest price
  =googlefinance(A2,"pe")	To become the Cost earnings ratio
  =googlefinance(A2,"eps")	To get the earnings per share
  =googlefinance(A2,"high52″)	To become 52 week high price
  =googlefinance(A2,"low52″)	To get 52 calendar week low price
  =googlefinance(A2,"marketcap")	To get market place 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 toll 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 lot go those y'all can very easily fill the post-obit 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 similar this:

• Now coming to dividends, I have observed Google's part is not that great. Its hit or miss. Sometimes it shows dividends sometimes information technology doesn't. Still, I found some other api nosotros can use to get the information related to dividend columns which is more reliable than Google's api. Its chosen IEX deject API.  Up until mid June 2019 they used to allow to become the dividend data for free without any registration. Only at present they crave you to annals and get an api key.
• So, lets try to exercise that. Caput over to the IEX Deject registration page and create account.
• Next cull the Get-go program which is free. Information technology provides 500K requests to the API every month which is more than plenty for a pretty big portfolio. Even if you lot request to get information from information technology multiple times a day every day for the whole month.
• Verify your account from your e-mail and so login to the iex cloud api account. Afterwards verification, log on and from the home folio 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 equally shown using the copy button. At present here I have ii options to become dividend amount into your sheet:

Current Dividend (Piece of cake to setup)

In this method, you volition need to copy this code and paste it into the script editor of your google sail as shown:

Once yous do this, yous are generally done. The formula to apply this script in a cell to become the dividend amount is:

= if ( isblank ( A11 ) , , GETDIVIDEND2 ( $A11 ," xxxx" ) )

Over again, y'all will need to supervene upon the xxxx with your personalized api fundamental from the last pace. A11 will exist the field of the ticker you are trying to get dividend info for. This should get y'all the electric current dividend for near The states listed stocks. For e.chiliad. for AAPL this will render the current latest dividend.  I wrote a function that uses your key to get the dividend from IEX cloud api. Then utilise that and the dividend frequency to get the annual dividend amount.

Sometimes a company has merely announced the most recent raise and non paid it out. In such cases, IEX returns the trailing 12 month dividend. For some stocks like BUD which pays out twice a year and has payouts where i is larger and next is smaller, this might not work. For these 2 employ cases, you can accept a look at the next method. Please let me know if yous come across any other stocks for which this script fails below in comments. I will endeavor and improve the script code to account for any problems you may notice. This detail formula usage is shown in line 2-4 of my sail if you download it. Practise call back to replace the xxxx with your API Key, since IEX no longer allows users to share the same key to get this data.

Electric current Dividend using Scraping (Easiest)

So in this method, we will merely apply the IMPORTHTML office google provides. We will then become the dividend paid out annually from a finance website and populate it in advisable column every bit shown. We employ the following formula in this approach:

= SUBSTITUTE ( alphabetize ( IMPORTHTML (" http://finviz.com/quote.ashx?t=" & A4 ," table" , viii) , 7 , two ) ," *" ,"" )

sometimes the formula doesn't get copied properly. And so endeavour to enter this formula manually in your google sail.

where A4 has the ticker of the stock. I have used this in the row half-dozen of my google sail provided. You may use this method whenever the first one fails(like in case of stocks like BUD). This method besides gets the latest forwards looking dividend yield just like the last one. You tin use any of the two methods for whatever number of rows in your google canvass depending on what works or doesn't.

• With the information about dividend per share, you can easily get information virtually 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, Bodily Shares Purchased & Actual Cost Basis. These are completely optional and will be used to summate your existent returns on original investment. And then, if y'all do Baste, this volition 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 iii-4 years back and turned Baste on. Now concluding year I bought some more WFC stock out of my own pocket and so my WFC holdings in my broker business relationship look like this:So, what I know is all my DRIP transactions are anything with fractional shares in quantity cavalcade and my whole number quantity transactions are made with bodily out of pocket money. Therefore, I but add up the quantity columns for whole numbers to get the Actual Shares Purchased column. I add upward the price basis for those transactions to become my entry for the Actual Cost Basis column. Important thing to remember is you lot must update these 2 columns any time you increase position in a stock with bodily money or sell some of your positions. Those transactions will affect values in these two columns. When you buy any new stock, value will be like shooting fish in a barrel to calculate first time. It should exist same as D(quantity) and F(Price basis). So, ideally y'all shouldn't be trading much and this update will exist very rare.
• And now, our beginning row is completely done:
• Now, in lodge to add new stocks, your piece of 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 downward from lesser right of the cell of outset row in a column. That volition basically call the same formula for that cavalcade for all your tickers. I have highlighted the columns for which you tin can do this. Afterward doing it for all these columns here is what we get:
• At present enter data in columns D, E, F, P,Q and R in that order. Rest of the columns you can again elevate downward as show in previous screenshot. Then now we are starting to go all of our data in the stockdata sail.
• 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 afterward receiving dividends using Baste. Please also note these are just numbers I made up, they might not be factually right, merely 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

At present that our stockdata sail is ready lets practise some graphs. Let's say you want to exist aware of which sectors you are allocated into and how much of your total portfolio.

• Lets add a new sheet chosen sectorallocation and add the sectors you want to track equally shown:
• Now, under the percentage column, in society to become the right allocation we are going to add this formula for utilities := ((SUMIF(StockData!P2:P12,"Utilities",StockData!G2:G12))/StockData!G13)*100
• What it will practice is, encounter the sectors we assigned in our main stockdata sail for whatsoever of the rows say Utilities, if so add together the values in those rows upwards and divide past the total value to get the percentage allocated to that sector. Similarly, do this for all the other rows. You lot tin can copy the formula, but you volition need to replace the Utilities with the sector of the row. So, nosotros get this:
• Now, lets add together a pie chart to the sheet and add together your data ranges from A2:A11,B2:B11. Later on that, we get the following:
• At the end y'all become a pretty dainty pie nautical chart showing your resource allotment in different sectors. This tin can help you be aware in what sectors you are more exposed and if you decide to purchase a new stock, you can see what sector you are very less exposed to and maybe await at a company there.

Monthly dividend tracking canvass

Then far, we created our stockdata sheet which shows our yield on toll, annual dividends, return with and without dividends etc. Our sectorallocation sheet shows how much or less are you exposed to diverse sectors. In add-on to these, let's create a sheet to track our monthly dividends. This sheet will aid us to create more than data and empathise our monthly greenbacks flows from dividends. It can as well eventually allow you to plot your dividend gains over few years/months of investing. You tin see yourself on your way to living off dividends.

• To practise this, add a new sheet and enter the columns as shown till December:Now for the first ii columns, go the information from your StockData sheets and populate them. In one case that's done, now you demand to manually fill up in what dividends you got every month from unlike stocks. This process can be a flake time consuming. Although, you should be able to get all the data from your broker. On my broker's website, I merely login and go to the transactions screen. Then filter to simply prove dividend transactions. This allows me to go through a few months of transactions at a time and fill this sheet rapidly. In add-on, we can also enter information for 2018 and previous years if possible, in new sheets. Yet, this depends on if you can get the information from your banker for previous years. Then using that data, we can add together some YOY(Year Over Year) percentage increases for your dividend stocks equally shown:
• At present, this gives y'all improve idea of how fast your dividend is growing every calendar month. You tin can even play around with some extra rows and add Year stop increase in dividends year over year for the total full etc. Now lets create a graph to show dividends received form companies in Q1 2019. Lets add a bar chart to the sail for 2019 Monthly dividends and apply the post-obit data ranges:
• Again, you can play effectually with the information ranges to modify this chart from quarterly to almanac ranges. That way you tin can get dissimilar blazon of graphs. Please experience gratuitous to check out my existent money dividend portfolio which uses this google sheet in practice.

Bank check out this article on creating your ain dividend tracking sail using IEX and google api'southward. Makes it look pretty like shooting fish in a barrel! Click to Tweet

Dividends over time sheet

If you have been using this dividend tracker for a couple of years, you will have enough data for a new chart. Y'all tin can track your dividends month over month and year over year. This allows us to see the dividend compounding effect over long term.

OK this is great! At present give me this canvass!

I do realize making your own excel from scratch can exist daunting even with the above given steps. And then, in case you only want a copy of the sample google sheet. Please enter your electronic mail beneath and I volition send you the canvass with easy to follow instructions on how y'all can set information technology up for your portfolio. Again, I think if you are buying private stocks for dividends, its utmost important to runway and measure your performance. So you know how you perform confronting the market place and can come across the furnishings of compounding in existent time. I usually update my personal sheet in one case a quarter and publish my quarterly results on this blog.

If y'all liked this article, it will be nifty help if you can share it with your social circle using any of the share buttons on the postal service.

As well check out how I get my fiscal news using a gratis Barron's & wsj subscription.

Yoda

I am merely a developer in my day chore and aspire to get a financially contained person. Learning and sharing knowledge is what I feel will help me the almost on this FI journey and then I created this blog.

How to Add Read More in Divi

Source: https://wealthcapitalist.com/investing/dividend_investing/how-to-make-a-dividend-tracking-spreadsheet/

Share this post