Tracking Monthly Stock Performance

Is tinkering with spreadsheets considered a hobby?

If it isn’t, it really should be.

When I updated my dividend portfolio spreadsheet to track annual forward dividend income, I talked a little bit about my love of spreadsheets and noted that I would continue to tinker with how I was tracking different metrics.

Identifying a Gap

While reading Paul’s portfolio thoughts over at Engineering Dividends, I had asked a question about tracking the month to month performance of his holdings.

I’ve looked high and low through my Fidelity brokerage account, and they do not readily make performance metrics available to account holders–at least not at an individual stock basis. There are rudimentary performance metrics provided for your overall account, however even that is limited to year-to-date as the most granular time period.

Since Fidelity doesn’t provide what I was looking for–which is a way to monitor how each holding has performed on a month to month basis–I figured that this was a gap to tackle with my spreadsheet.

Creating the Spreadsheet Formulas

My dividend portfolio spreadsheet makes extensive use of the free IEX Trading API to retrieve stock quotes and dividend information.

Naturally, I turned to the API to see how I could tackle this problem.

Looking through the API, one will find that historical stock prices refer you to the section on charts. The charts provide everything from the 5-year chart information down to the 1-day chart, as well as the ability to retrieve a specific day’s information.

The latter is what I had hoped would work, but unfortunately that is limited to only the trailing 30 days and I was hoping to retrieve data for a rolling 12-month period.

Ultimately, after a good deal of trial and error, I settled on using the Year-to-Date chart and parsing out the specific data that I wanted.

As an example, here is the link and resulting data for Apple stock:

https://api.iextrading.com/1.0/stock/aapl/chart/ytd

Now, if that doesn’t get you excited, there must be something wrong with you! 🙂

So, now we have the YTD data and we need to retrieve the closing stock price for the last day of every month.

As I don’t want to bore you with too many technical details, I will say that the YTD data from the API is stored in an array, therefore we need to identify the correct position within the array that represents the last trading day of every month.

IMPORTJSON("https://api.iextrading.com/1.0/stock/"&$A5&"/chart/ytd",networkdays($C$4,WORKDAY(EOMONTH(C$4,0)+1,-1),Lists!$E$2:$E$10)-1&"/close")

The above statement is part of the formula used within my spreadsheet, and I will break this down into pieces for additional explanation.

IMPORTJSON("https://api.iextrading.com/1.0/stock/"&$A5&"/chart/ytd"

Here we have the call to IMPORTJSON, which is the function we use to retrieve information from a website or API and make the data available to us within the spreadsheet. The first parameter is the URL that we want to retrieve. The bit that looks a little cryptic–&$A5&–is a reference to the column in my spreadsheet that stores the ticker symbol.

networkdays($C$4,WORKDAY(EOMONTH(C$4,0)+1,-1),Lists!$E$2:$E$10)-1&"/close"

The “networkdays” function provides us with the number of working days between two dates. This function takes three parameters: 1) Start date; 2) End date; 3) Holidays.

For my purposes, the start date is locked in as the first day of the calendar year, which is represented by the reference to “$C$4” in the formula. The end date is determined by using the “workday” function. The last piece of information that we need to include is the holidays that the stock market is not open. For this I created a lookup reference on my Lists tab and included the annual trading holidays.

It is important that we include the trailing “-1” as well, because we are using the number to determine the index position in the array of data. Array indexes start at 0 as opposed to 1, therefore we have to subtract 1 from the day of the year to get the appropriate index position.

The last bit of information that we add to the formula is “/close” and this indicates that we want to retrieve the data assigned to the attribute labeled as “close”, which in the case of our data is that day’s closing stock price.

Hopefully all of the above was not too complicated, and if you don’t care for the details all you need to know is that the formula will retrieve the closing stock price on the last day of a given month.

By using this formula, I’ve been able to create a tab in my spreadsheet that will automatically update every month and retrieve the closing stock price on the last day of the month. Now that I have everything created, I don’t need to touch the spreadsheet at all throughout the year and I will instantly have up to date information.

And when it comes to spreadsheets, automation is a beautiful thing!

Tracking Monthly Performance

All of the above produces a handy reference that captures the closing stock price for every month and then calculates the monthly performance of each stock. I’ve also included an overall YTD performance calculation for reference.

Here is what the monthly stock prices look like:

With the automation built-in to the spreadsheet, the above display will automatically update on November 1st to include the closing price recorded on October 31st.

By using the stock prices from month to month, we can then calculate the performance on a monthly basis as well as the performance for the full year-to-date as shown here:

You may notice some of the color coding in the screen shot above, and I’ve done that just to highlight a few key metrics that I might want to pay closer attention to:

• Any decline in price up to a 10% loss is denoted in red text
• Losses greater than 10% are highlighted with a red background
• Gains greater than 10% are highlighted with a green background

At a glance this now allows me to see if there are any significant price movements in my holdings that may warrant me investigating further for a potential buy–or hopefully less likely a sale.

On the far right we have the YTD gain and this follows the same formatting as the monthly stock prices.

One important thing to keep in mind here is that this information does not include my personal performance or any dividend payments. It is purely the market price of each stock independent of anything else.

Summary

Time will tell how much I utilize this information, but as I was curious about how my individual stocks were performing on a monthly basis and my brokerage account did not provide this information easily, I decided to create it myself.

Now I have a handy reference in my spreadsheet that updates automatically every month to capture the closing stock price and calculate the monthly performance.

If you’re interested in duplicating this information for your own portfolio, you should be able to use the information provided here but please feel free to reach out to me if you need help.

What do you think of the latest addition to my spreadsheet tracking?