Tracking Monthly Stock Performance

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

IEX API Sample Data

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:

Monthly Stock Prices

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:

Monthly Stock Performance

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?

8 thoughts on “Tracking Monthly Stock Performance”

  1. Looks great, DivvyDad. I like the format and the highlighted cells to draw your attention to them. I believe it will be a handy reference. I agree it’s hard to know how much you’ll use it, but you certainly will at some point, and when you do, all the info will be waiting with no additional effort required.
    I’d use the data at least every month, as I mention movers from my portfolio in every ‘portfolio thoughts’ post.
    One possible enhancement to consider might be ranking the percentage movers for each month and YTD. This would certainly be more helpful for portfolios with a large number of holdings.
    Keep up the terrific efforts!

    1. Thanks ED! If nothing else it was a fun exercise that let me include a few new things in my spreadsheet that might prove useful elsewhere.

      I’ll have to give the ranking some thought, but any additional changes might get deferred as I started exploring selling cash secured puts today and might dip my toe into the options water next week. If I do that, I will certainly need to create an options tracker. 🙂

  2. Here we have another thing in common as I am a bit of a spreadsheet guy too. I track only a few metrics, but they help me for quick reference. I tend to buy and hold so, looking at the data is more entertainment than something I’m taking action on. Tom

    1. Most of our financial data has been tracked in spreadsheets since about 2000-2001 when our net worth was hovering around $0.00. It can be quite entertaining to look back and review some of the data, and now with the portfolio spreadsheet I suspect it will be similar to you as I don’t anticipate selling anything all that often–but could see using this new data to watch for stocks that have dropped and might be a good buying opportunity.

    1. Hi Robin, this is in Google Sheets although most of the old financial spreadsheets that I mentioned in the comment above are in Excel.

  3. Hi DivvyDad,
    Great work and thanks for sharing! I am not that good with Excel but I will need to give it a go. I would definitely be interested in having this information automated, as I am currently filling in my portfolio update manually in a spreadsheet at the end of each month (facepalm)…
    BI

    1. You’re welcome BI, and the good thing is that you really don’t need to reinvent the wheel as there are so many templates that are shared to give a head-start. In my post about tracking forward income, linked below, I share a link to the spreadsheet from Two Investing that I’ve used as my starting point.

      https://divvydad.com/tracking-annual-forward-dividend-income/

      Also, to think of it from a different perspective, doing the manual updates can be positive as it gives you a much closer appreciation for the data. Sometimes when things are automated, we tend to gloss over them or ignore them–and that can be dangerous as it relates to investing. If it is a time drag, I would encourage looking into some automation, but also know there may be some trade-offs.

Leave a Comment

Your email address will not be published. Required fields are marked *