Tracking And Modelling Stock Dividend Portfolios In Google Sheets - Part 1

by: Stefan Redlich

Summary

This series will gradually develop a fully functioning Google Sheet to track and model stock performance and dividends.

Part 1 covers the expected output and features of that tool and generates the appropriate raw data structure used in later stages.

Users/Readers can interactively track the progress inside Google Sheets and comment.

Mutual learning from each other.

What is this all about?

With this article series I want to design a reliable, real-time, convenient and powerful tool inside Google Sheets, the Dividend Stock Portfolio Tracking Google Sheet (DSPT-GS) to track and model stock/dividend portfolios.

The approach is that I will make some proposals and then invite everybody to comment on that. As we iteratively work towards improving and enhancing the tool, I will gradually share the document so that everybody can edit it and make changes.

Part 1 of this article series covers the following:

1. Outline the requirements and structure them using the MoSCoW method (a prioritization technique clustering requirements into "Must haves", "Should haves", "Could haves" and "Won't haves")

2. Encourage open discussion in the comment section as to if you agree to that prioritization and if you feel something is missing

3. Designing the raw data sheet so that it is suitable for later calculations and transformations. This includes explaining the most important formulas/calculations for easy replication.

4. Getting historic exchange rates and preparing the raw data for multi-currency reporting

The requirements...

Framing these requirements from a user point of view (user story) would result in something like:

"As a private investor I need a tool which models and tracks my stock portfolio and dividend income performance. This tool should be the go-to place to check out progress and projections. I want to invest at least time as possible and be presented with an easy-to use, intuitive and real-time reporting and analysis tool."

Do you agree with the prioritization and the features mentioned? Is there something you would add or drop? Does this make sense at all?

Before we get started here is an overview of the development stages of the DSPT-GS.

Click to enlarge

Let's get started: Getting the raw data

Now, we finally get to actually building something. Whatever analysis you want to run later on, the quality and value and the effort required to conduct this is primarily determined by the structure of the raw input data you collect from all your transactions and in real-time from Google Finance. For the sake of simplicity I am ignoring stock splits for now and concentrate on these three types:

  • Buying shares ("Buy")
  • Selling shares ("Sell")
  • Receiving Dividends ("Dividend")

For these three types of transactions the raw data needs to be structured meaningfully. Also that part includes automatic conversions of transaction currency into reporting currency.

Here is the final Google Sheet that will have been designed after this first part.

In Part 1 the DSPT-GS consists of four sheets:

  • RawData (input section for transaction data)
  • Overview (future output section now needed to select home and reporting currency; these will be explained subsequently)
  • FX_Rates (automatically updated historic exchange rates for tailored currencies)
  • The Project (includes overview of requirements and outlines the process for development)

A. Designing the "RawData" Sheet (1/2)

In its current form the sheet consists of 27 columns (thereof 7 columns which require input from the user). The remaining 20columns are. The most important calculations will be presented subsequently.

Column D: Stock name

To retrieve the stock name for a ticker (= cell C2) on Google Finance we can use the following formula:

=if($C2="","",GOOGLEFINANCE($C2,"name"))

Column M: Last Shares = outputs the number of previously transacted shares. To accomplish this we make use of Google's powerful SQL-like API.

=sum(QUERY($A:$K,"select sum(NYSE:E) where C = '"&$C2&"' and I < "&U2&""))

To understand this formula we need to explain this formula from inside to outside. With Query we trigger a query on the area "$A:$K". We then take the sum of column E if two conditions are met: (1) columns c equals the ticker in the current row and (2) only the rows which are above the current row should be considered. To achieve the latter we have created column I "Row helper" and column V "Row helper". The reason for having this as a duplicate is that for some reason you cannot make this restriction inside the data range of the query, i.e. typing and I < "&I2&" does not work as it results in a circular error. If someone has a better solution to this I'll be happy to hear. Finally, we need to put a sum outside this query function in order not to return the column header.

Column N: Accumulated shares = outputs the number of total transacted shares by ticker. Again we use the Query function:

=sum(query($A:$G,"select sum(E) where B = 'Buy' and C = '"&C2&"'"))-sum(query($A:$G,"select sum(E) where B = 'Sell' and C = '"&C2&"'"))

The only thing worth mentioning here is that we need to distinguish between "Buy" and "Sell" transactions to derive the accurate number of currently accumulated shares. What's more, dividends despite entered in with 1 as the number of shares in column E are not considered here.

Now it's time to make the first preparations for our multi-currency reporting feature. Again, let's start with the user story:

"As a private investor based in the US who does not only own US stocks I want to see how my holdings break down by native currency. I also want to be able to switch my home currency USD into another currency, e.g. EUR, in order to compare my portfolio with other EUR-based portfolios. On top of that I want to simultaneously see my portfolio performance and income in both currencies."

So, in fact this feature should enable three things:

1. Convert values for transactions with historic exchange rates into selected exchange rates

2. Convert real-time stock and portfolio information from USD into another currency

3. Both home currency (the currency in which the user enters data on the RawData sheet) and reporting currency (the additional currency which the user can select) should be displayed simultaneously.

We will model this by allowing the user so select both home currency and reporting currency. Let's start with getting the relevant exchange rates.

B. Designing the "FX_Rates" Sheet

This sheet collects all historic exchange rates beginning from the starting date specified in cell A2 for the currency pairs defined in row 1. Currently, the data range covers the period from Jan 1 - Dec 31, 2016. To import these exchange rates we again use Google's powerful GoogleFinance function:

=GoogleFinance(C$1,"price",date(year($A2),month($A2),day($A2)))

With A2 equal to Jan 1, 2016 and C1 representing the currency pair USDEUR this formula would return:

Date

Close

1/1/2016

0.9208

Click to enlarge

As we do not want 4 cells to be output we need to restrict this to the bottom right cell. As Google treats this result inside the GoogleFinance function as a matrix we can specify the exact position of the "0.9208" value by placing the index function in front of this:

=INDEX(GoogleFinance(C$1,"price",date(year($A2),month($A2),day($A2))),2,2)

The first "2" specifies the row and the second "2" the column.

For some reason these GoogleFinance formulas do not always work perfectly, i.e. that for some dates we get errors despite the formula being correct. As these exchange rates here are only an approximation of what the exchange rate right at time of transaction was I deem it sensible that in case the function returns an error it should simply output the exchange rate from the day before. To accomplish this we put an iferror statement in front of the formula:

=iferror(INDEX(GoogleFinance(C$1,"price",date(year($A2),month($A2),day($A2))),2,2),C1)

Finally, as this period stretches until Dec 31, 2016 we need to restrict this formula to only apply if the date is not greater than today:

=if($A2>TODAY,"",iferror(INDEX(GoogleFinance(C$1,"price",date(year($A2),month($A2),day($A2))),2,2),C1))

Now that we have set up the exchange rates for further processing we can integrate this into our RawData sheet.

C. Designing the "RawData" Sheet (2/2)

To enable the tool to handle and output both home and reporting currency we need to convert input values and create separate calculated columns for the reporting currency. In total this requires 7 new columns. I am only going to explain those with new formulas.

Column J = Stock price reporting currency

=VLOOKUP(date(year($A2),month($A2),day($A2)),FX_Rates!$A:$U,MATCH(currency_pair,FX_Rates!$1:$1,0),0)*F2

Here, we lookup the data in column A in our "FX_Rates" Sheet. The lookup column is then retrieved by matching the concatenated home and reporting currency string (e.g. "USDEUR") with row 1 in "FX_Rates". Following the same logic, fees in Column K are converted.

Now, that we have the raw data inputted and converted we can calculate the actual values (quantity * price) in both home and reporting currency (see Columns L, O, P and S).

Request for feedback

This concludes the first part of this article series.

Please let me know in the comments section if you found this helpful or if it included too much detail or if it was irrelevant at all.

I am also looking forward to comments in the linked Google Sheet. If you have ideas how to speed up calculations these are also highly welcome. You can already download the document if you want to play around with it in its current stage.

If you like my writing please press the "follow" button.

Disclosure: I am/we are long AAPL, FRA:BAS, BP.L, CSCO, KO, CL, FRA:DRI, XOM, FRA:FME, FRA:FRE, GILD, JNJ, MCD, PM, PG, RDSB, UN, DIS, PEP, VZ, V, WFC, GIS, BGS, SEP, MSFT, CBA.AX, MAIN, IBM, CTL, TNK, HCP, ABBV, FIG, CLDT, RY, EPD, QTS, DFT, GM, FRA:BAYN, CZNC, OHI, CM, T, JPM, STAG, HPT, FDX, FRA:DAI, AINV, SNH, DHT, FRA:BMW3, FRA:CBK, FRA:ADS, FRO, HON, STWD, PEB.

I wrote this article myself, and it expresses my own opinions. I am not receiving compensation for it (other than from Seeking Alpha). I have no business relationship with any company whose stock is mentioned in this article.

Additional disclosure: I am not a licensed financial professional. This article is for informational/entertainment purposes. Be sure to consult a professional and do due diligence before investing in equities, as losses up to and including all capital invested can be incurred. I may add to any of these positions at any time.