The GoogleFinance function on Google Sheets is a great tool for investors looking to build portfolio tracking spreadsheets or do stock analysis inside their own spreadsheets. I use the GoogleFinance function in Google Sheets for all kinds of analysis and tracking. It’s one of my favorite tools I use online.
Using the GoogleFinance function for dividend data is a bit tricky. The GoogleFinance function does not have any built-in attributes or parameters that let us pull in things such as dividend payment, ex-dividend date or dividend yield. As such, to do a proper dividend investing sheet with GoogleFinance, we have to incorporate a few work-arounds.
FREE DOWNLOAD: GOOGLE FINANCE CHEAT SHEETFeaturing:
- An easy-to-print, single-page cheat sheet
- Quick reference for formulas and available attributes for the GoogleFinance function
- Quickly build formulas for real-time or historical stock data in your Google Sheets
Here are the steps to build a dividend tracking spreadsheet using Google Sheets and the GoogleFinance function:
Step 1: Setup the Google Sheet and pull in the stock data with the GoogleFinance function
In our example, we’re going to use a set of stocks such as: Walmart (WMT), Apple (AAPL), McDonald’s (MCD), Coca-Cola (KO), Microsoft (MSFT) and Procter & Gamble (PG). We will start off with three headings in the spreadsheet: Stock ticker, company name and current price.
First we will add these tickers in our first column as follows:
Next, we will use the GoogleFinance function to pull in the name of the company and the current share price. The two formulas we will use are as follows:
- =GoogleFinance(ticker, “name”)
- =GoogleFinance(ticker, “price”)
Since we have a list of tickers already in the spreadsheet, we can simply call the cell such as A2 to get WMT. Then we can copy the formula down through the column without having to type the ticker each time. This also means if we change out the tickers eventually, the spreadsheet will still work and we won’t have to update formulas.
Next you can highlight both cells with formulas (B2 and C2), then use the small square at the bottom right of the highlighted area and pull it down through row 7. This will copy the formulas for each remaining cell. You should now see something like this:
Step 2: Pull in dividend data into the google sheet
It’s now time to pull in dividend data. But, like we said earlier, GoogleFinance doesn’t have an attribute for dividend data (despite having many attributes for various stock metrics). So, we’ve got to do a work-around.
To accomplish this, we’re going to use a different function called IMPORTHTML and we’re essentially going to pull data from a third-party financial website and hone in on the dividend value. To get started, we’ll focus on Walmart (WMT). Here is the function we’re going to use.
- =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/WMT”),”table”,2),6,2),” “)
Let’s break this down piece by piece to help you understand what is happening here:
- IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/WMT”),”table”,2) is pulling the stock data table you see on the Yahoo! Finance quote page for Walmart. The 2 parameter you see is telling the function to grab the 2nd table. The first table is the first chunk of data on the left. The second table is the chunk of data on the right. See the image below:
- We wrap the above in INDEX( .. ,6,2) in order to pull the data from the table’s row 6, column 2. If you go to the Yahoo! Finance page, you can see that the dividend data is in the 2nd column, 6 rows down.
- Then, we wrap this in SPLIT( .., ” “) which tells Google to split the text pulled from that cell, separate it whenever it encounters a space (” “) and then put it into different cells. More on this in a minute.
To help you visualize what data is being pulled, here is the Walmart quote page from Yahoo showing you where the 2nd table is plus the row 6 and column 2 text:
When you use the function, you will get both the 2.20 and the 1.57% values in two cells adjacent to one another.
Now we will want to make this a generic function instead of hard-coding “WMT” into it. That way we can copy it down the column and make it work for each stock. Let’s do that now. Here is the result and what the function looks like:
Step 3: Fix the dividend yield
You’ll notice that the yield is showing up as a negative number. The reason for this is because if you look at the Yahoo! page above, you’ll see the dividend yield is inside parenthesis. The Walmart data was “2.20 (1.57%)” – so Google is interpreting this as a negative number.
There are two ways we can deal with this:
- Option 1: Make another column and use the absolute value wrapper. Then just hide the column with the negative yield.
- Option 2: You can further wrap our big function to only pull the first piece of data. Then we can use the dividend and the current price to determine the yield in a new cell.
Let’s demonstrate both options.
Option 1: Use absolute value and create another column
Using the =abs(cell) function, we can get the absolute value of the dividend yield into a new dividend yield cell as follows:
Now we don’t want two dividend yield columns as that would be confusing. So you can hide column E to get it out of the way. Right click the column and select HIDE COLUMN.
After that, you’re done!
Option 2: Further wrap the dividend data call, then calculate yield separately
In this option, we’re going to modify our function from above. We’re going to add another INDEX wrapper and identify row 1, column 1 of the data to isolate the first piece of data. Essentially, we will do =INDEX( [previous function] ,1, 1). It will look like this for our Walmart (WMT) call:
- =INDEX(SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/WMT”),”table”,2),6,2),” “),1,1)
Here is what it now looks like in the Google sheet:
Now we can create a new formula in the Yield column where we divide the dividend by the current price to get the yield.
Don’t forget to change the formatting to a percentage (%).
Step 4: Tidy up the formatting
Ok, the hard work is done. Now we just want to pretty this up a bit. Let’s make the headings bold, fix up the column widths, make sure column C and D is in currency format, and maybe center align columns C, D and E. After doing that, we’ve got this:
How can you use this dividend Google Sheet?
So you now know how to pull in dividend data dynamically for stocks. You’ve got the dividend payments and yield handy. What can you use this for? Here are some potential suggestions to consider:
- Create a dividend income tracking spreadsheet – If you add a column for number of shares you own, you can then calculate the annual dividend payment collected so that you can begin tracking annual dividend income.
- Calculate yield on cost – If you add in the cost basis for a stock position you have, you can dynamically calculate your yield on cost moving forward. Because we’re pulling in the dividend amount dynamically, it will update as the company increases its dividend payment. Then, in addition to calculating yield based on current share price, you can calculate the yield on initial cost of the position.
- Map out monthly dividend income – If you want to manually enter some information, you can pull the data manually on when your particular stocks pay out its quarterly (or semiannual) dividend payments. Then, you can add columns for each month and get a great overview of how much income you might expect for each month of the year.
- Use the dividend data to calculate total return of your positions – You can add a column for the stock price one year ago, then calculate the return over the last year. With this share price appreciation profit now identified, you can add in the dividend income and calculate total return of the position.