Hello again everyone! It’s Thanksgiving week and as a Teacher I’m thankful for that. With this down time I’m saying my prayers for all the gifts God has shared with me and reminding myself of what I really deserved. I’m incredibly thankful I didn’t get that! I hope through all the stress this holiday season may bring you’re able to step back and see all the gifts He’s shared with you as well!
With that I thought, given the free time I have today, I would attempt to share some valuable information on building a respectable portfolio tracker in Google sheets.
Before we begin, you should know that building a truly impressive portfolio spreadsheet in either Google Sheets or Excel is going to take some time. Admittedly, the spreadsheet I’m going to share with you in this post took a few weeks to get everything functioning properly. Additionally, I’d say no spreadsheet is ever truly complete. No matter how many you create, or how thoughtful the construction, inevitably there will be something missing. I guess that’s just the nature of slicing data!
That said, If you prefer to skip the DIY approach, you may find value in this highly capable spreadsheet I’ve made available, Optionboxer Portfolio – Stock and Options Journal.
Post Agenda
- What Data Do I Need?
- What do I Want to Know?
- Settings Page
- Transactions Page
- Overview Dashboard Page
- Calculations Page
Data Needed for a Portfolio Tracker in Google Sheets
To begin, we have to get some data. Typically, this is easy to ascertain. Information like number of shares owned, purchase price, and date purchased are a minimum requirement. However, the sky is really the limit and the more data you bring in, the more options you’ll have with regard to viewing that data.
Ultimately, we’re trying to do one of two things. One, we’re simply showcasing it for easy viewing. At least, easier than any brokerage could. And two, aid our decision making process in the future.
Showcasing the data is relatively straight forward. However, it’s only necessary for those of us that want a closer look at our investments. The real usefulness of a thoughtfully crafted spreadsheet is from the stories it can tell. Here is a simple example using the short term Treasury ETF – SGOV.
Immediately you can see what I’m seeing, the dividends being paid is slowly going lower. So slow in fact that over the last 4 months I may not have even noticed the steady decline if it wasn’t for this portfolio tracker in google sheets.
Now, I realize that is an overly simplified example using an asset that most would agree isn’t meant for growth. However, if that were any other asset, I wouldn’t feel great about the investment as my dividends decline each period. Thus making my point, without a decent spreadsheet I may have missed this underlying problem. A problem, that may need to be addressed at some point in the future.
With that, here is the data I believe is absolutely vital to creating a robust portfolio tracker in Google sheets.
- Investment Date
- Investment Ticker Symbol
- Transaction Type
- Number of Shares
- Total Cost of Transaction
- Current share price
- Fees
- Account Designation (if applicable)
- Strategy Designation (If applicable)
With these criteria, we can begin pulling the information into other areas of the spreadsheet to expand our investment view. In the upcoming sections we’ll look a little closer at what may be possible using this specific data.
What Do I Want to Know?
This, I feel, is where most people get bogged down in their attempt at creating a great spreadsheet. Admittedly, this was true for me as well. Unfortunately, only time and experience will illuminate the answers you’re seeking. Still, we have to get started somewhere and as we do we’ll begin to look at our spreadsheet with a questioning eye. Can I see X, or is Y better here?
Not to be too philosophical, but it’s kind of funny to me, the answers you seek are in the questions you ask? The only way to know what questions to ask is to build a portfolio tracker and begin uncovering what may be possible.
Regardless, here are some basics to get the ball rolling down the hill.
- What is my total portfolio value across all accounts?
- How much cash do I have available to invest?
- What is my total gain or loss?
- How much have I made from options, dividends, interest?
- What is my yield on cost?
- How has my portfolio done relative to the S&P 500?
- How much capital have I contributed across my accounts this year?
As you can see, the questions will start to pile up quickly. I think anyone willing to invest in the first place wants to know if they’re making good decisions. The brokerage will provide you with raw information but tedious doesn’t even begin to describe the task of calculating those data points each and every time we need to know something.
Moreover, we’re putting a lot of faith in our mental faculties to remember each and every metric, each and every time we want to consider a data point. In that instance, we’d have to ask the question, find the raw data, perform the calculation, and consider the implications of that information. With a robust portfolio tracker in Google sheets, we simply need to consider the implications and take action.
Settings Page
Admittedly, if you’re only wanting to build a simple spreadsheet, this page may not need to exist. However, much of what I use this page for is the starting point for information that will be needed elsewhere. Without being long winded, here is an image of my settings page to help you uncover what may be useful to your own.
As you can see, moving from left to right in that image. I start with the portfolio initiation date. Anything entered into this portfolio spreadsheet must come after that date. If you’re entering data prior to that date in a spreadsheet I’ve built it’s likely that data will not be collated in the subsequent data or graphs.
From there I move through each of my accounts, I list out my categories or classes for diversification tracking, along with my preferred options strategies. As an options trader, it only makes sense that I relentlessly cull out underperforming strategies. The remaining columns you see that are “greyed out” are for use in creating lists on future pages. Again, not really necessary but does make creating that list a bit easier and it’s always there in case I need the list again later.
Transactions Page
The transactions page is the most important page. Truly, if you’re wanting to create a worthwhile spreadsheet you’ll need to be sure and get this page right. Now, I’m not suggesting I’ve perfected this page but I have settled here for now because it does what I’ve asked it too.
As a brief aside, here is the very first spreadsheet I ever created to track my trading. Kind of crazy to me how far its come.
But why am I showing you this old dusty spreadsheet? Well, mostly to show that I’ve tried this many, many times over and have decided that building it similar to the transactions page image makes sense. Moreover, I’ve shared it with you as a source of inspiration or idea generation. You may not have considered the usefulness of tracking the average fee per trade :P. I can’t say I found much use knowing that one.
In any case, you can see from the transaction page image what I’m collecting and how I’ve structured collecting that data. To the far right of that page you’ll see “additional data”. Rather than have that information collected for each transaction, which is fairly common. Instead, I collect that information once for each symbol and call the information as needed. Thus eliminating the shear volume of data being stored within the spreadsheet.
Lastly, the annual dividend and the market price are both retrieved from a formula. Here is each formula.
Annual Dividend – =IFERROR(IF(L8=””, “”,LEFT(SUBSTITUTE(INDEX(IMPORTHTML(“https://finviz.com/quote.ashx?t=”&L8, “Table”, 10), 7, 2),”*”, “”),5)*1), “”)
This formula uses the symbol from cell L8 to search Finviz.com for the dividend data. It then also extracts just the dividend information from the data it finds.
Market Price – =IFERROR(GOOGLEFINANCE(L8, “Price”), “”)
This formula again uses the symbol from cell L8 to call the data from Google Finance. This function, and many others available from Google Finance are free and generally reliable. Here is a complete list of the data Google Finance has available but I do wish they included dividend data, that would make things a bit easier.
Overview Dashboard Page
No portfolio tracker would be complete without a central hub of information, or dashboard. This page brings everything into one place to show us a broad overview of our investments and how they’re performing. Said another way, it’s the reason we create the tracker in the first place, usually.
I won’t share it again, but you can see my overview page in the very first image of this post. I have created mine to show all of my accounts together and separately. By selecting the dropdown from the top left each graph will update, along with the data table below to show me just that accounts holdings. Admittedly, a cumbersome process with very little usefulness.
From there, I’ve included the ability to view each assets growth and dividends separately as well. I’ve listed the daily, weekly, monthly, and 6 month value changes to the far right. This is just an indication of whether my portfolio is growing and shrinking over time from one easy to view place. I don’t have to wade through anything to uncover what is a very simple calculation.
Lastly, down below is the data table housing several important pieces of information. It’s a place I find detailed metrics on almost a daily basis. Namely, how much did I invest in VUG, or what is the total I’ve received in dividends from SCHD? All of that is calculated on a calculations page and pulled into this table for easy reference.
Before I move on, you’re probably wondering why I’m not sharing specific detail with regard to the construction of this information. Easy, I’m only wanting to share the insights for building the spreadsheet, not necessarily the steps I took. While it may not be believable, I’m sharing the most difficult part of this process.
Calculations Page
Oh man, the calculations page! Honestly, I could spend hours sharing and dissecting this page but it wouldn’t ever be enough. The truth is, this page is a work in progress to say the least. Usually, just about the time you figure one thing out and have it showing as intended, you find another way to implement that finding. As such, I won’t be diving to deep into this page.
Rather, I hope you’ll see it as a blueprint of sorts for creating your own portfolio tracker in Google sheets. All of the data I’ve pointed to throughout this post comes from just this one page. Over time and through trial and error you’ll learn where data can be aggregated or outright eliminated to achieve the same result.
First, take notice of the different color section headers. Each different color indicates a different table of data. The first blue table is all of my investment data pulled directly from the transactions page. This was necessary so that I could create the account view drop down from the overview page. If I didn’t need that functionality, I could have just called the data from the transactions page.
Similarly, the yellow section is all of my options data. To avoid having two different transactions pages, I just implemented a check box to the far left of the transactions page for each line item. If that box is checked the calculations page knows to pull that information into this yellow section.
The last section, in orange, is the data used to produce the bottom table from the overview page. To get it to update as I select a different account here is the formula I used.
Account Selection Calculations Formula – =IFERROR(IF(‘Account Summary’!D1=””, SORT(unique({$F$3:$F;$Y$3:$Y}),1,True),IF(SORT(unique(filter({$F$3:$F;Y3:Y},{$K$3:$K;AD3:AD}=’Account Summary’!D1)),1,True)=””, “No Positions”, SORT(unique(filter({$F$3:$F;Y3:Y},{$K$3:$K;AD3:AD}=’Account Summary’!D1)),1,True))), “No Positions”)
Once the overview page drop down is selected, this formula filters the available assets by that account. Honestly, this was a tough one to figure out but here it is in case you want to do something similar.
Final Thoughts
Finally, the end of what has proven to be a daunting post. There is so much I wanted to share but everything is a discussion in its own right. For instance, I couldn’t share with you specific calculations for finding cumulative cost without first having a long discussion about array formulas and where to pull the data from. Initially, I’d wanted to share that information but as I got into it, I realized the post would generate more questions than answers for you.
Therefore, I elected to share just my thoughts around building a portfolio tracker in Google sheets. You may or may not agree with that decision but I know once you start work on a complex spreadsheet of your own you’ll see what I mean.
That said, I have tried to point toward my thought process and provide the framework for getting started building your own unique spreadsheet. It really is a labor of love and goes with investing like peanut butter to jelly. If I can ever assist you in the process please don’t be a stranger, reach out to me in the comments or via e-mail and I’ll share everything I know.
Until the next post.
God bless,
Jeff
