MAKE A STOCK TRACKER WITH GOOGLE SHEETS
An Old Dog Learns a New Trick
I very much enjoy personal finance. Also, unlike many of my financial independence minded writer friends, I enjoy individual stock investing and research. Just recently in mid-2022 I discovered how to share portfolio information using Google Sheets. If you're not familiar Sheets is the free Google equivalent of Microsoft Excel. It's a spreadsheet maker and the real beauty of Sheets is twofold: First, it's free and any spreadsheet you make is stored in the cloud. Second, and my favorite aspect, is Sheets are shareable with anyone who gets the link to your creation. Lots of people probably knew about this for a long time but I'm slow on the technology uptake. Hell, I didn't even know how to take a screenshot until I started this stupid blog 5 years ago.
I made a stock tracker with Google Sheets
Why would anyone want to share spreadsheet data? Well, I wanted to use it for financial tracking so if I put out a portfolio like the Malevolent Missy portfolio anyone can look at (almost) real-time updated results for every holding. This is a portion of what Missy owns:
The problem with a photo is that is only a snapshot from Excel. This is a link to Malevolent Missy's portfolio updated to the nearest 15 minutes from the stock tracker I made with Google Sheets: Missy Portfolio in Sheets? I thought that was pretty cool when I first discovered it.
Then one day while I was dicking off reading generally useless content on Elon Musk's Twitter Machine I saw a long post from a professional investor and stock picker. I generally like many of these accounts and this guy had a list of 20 stocks with high margins he considered undervalued in October of 2022. He had a little blurb about each of the 20 and I thought "Hell, these are all really high quality companies/businesses" and I already owned 4 of the 20 in my own portfolio. So, I wrote down all 19 names (one was a foreign company that's harder to track) and their prices on October 18, 2022 along with the price of the popular S+P 500 index that day. I called the portfolio the Twitter Guy List and thought it had a good chance of beating the S+P 500 over a long period with fairly conservative investments. Most of Twitter guys list will not hurt you too badly if you own them. Here is a screenshot of them:
You can see the guy is beating the S+P 500 index by almost 6% in the first 6 weeks or so. The stocks highlighted in yellow are the ones we own in our Smidlap Portfolio. I wish I would have written down the Twitter account name to give the guy credit but I did not.
If you want to make your own Google Sheets Stock Tracker
The key to the whole thing that goes out to the web and retrieves stock data is the Googlefinance function. The easiest way to explain how I did it is by example:
If you have never used Google Sheets here is the link: https://docs.google.com/spreadsheets/u/0/ where you can make a blank sheet to track anything, not just make a stock tracker.
Look up above at the graphic for the Twitter guy portfolio. On row 23 I am tracking Adobe stock. So in B23 I just add the stock symbol "ADBE."
Then, for the company name and current stock price in A23 and D23 the formulas are "=GOOGLEFINANCE(B23,"name")" and "=GOOGLEFINANCE(B23)" where B23 is the ticker symbol I added for the company manually in step 2. I also added the trade time in column G using =googlefinance(B23,"tradetime"). I only added that so any user can see the most recent update.
To make the whole thing a tracker against an index I input the cost at the time of purchase (either real or just for tracking purposes) in C23.
The calculations in columns E and F are pretty straightforward. In E23 the formula is just =D23/C23-1 and F23 is =E23-E$4 where E4 is the gain for the index.
If you are building from scratch you can just copy and paste special to add more holdings.
Tracking the index and ETF's is more a pain in the ass with Google Sheets
One thing I found is that tracking something like the S+P 500 index or VTSAX for comparison purposes doesn't always work as seamlessly as it does for individual stocks. For instance, the ticker for the SP 500 is INX. But, if you want the name, today's price, and trade time like in the ADBE example you have to input the symbol as .INX in B4. I just figured that out as I was writing this.
Anyhow, why would you make your own when I am giving you the sheet to use and alter as your very own? After I started tracking the Twitter Guy's excellent companies I copied and pasted my way to tracking 40-50 more companies against the S+P 500 just for fun. All in all I think it's an interesting list of stocks to begin research. Oh, and on the right side of my Google Sheets stock tracker is a little quick and dirty stock evaluation tool but that's a story for another day. So, take a look at what I've been tracking. There are some interesting stocks in there and with full disclosure once again the ones I own are highlighted in yellow. Here is the link if you want to use what I've built for your own stock tracker. You can just substitute your own symbols over the ones I have in there. I called this one a value stock evaluator.
Anyhow, all this self-education took a long time but it was fun for a schmuck like me with time on my hands.
Have you ever gone through a long exercise like this just to teach yourself something? Did you look at the list? What the hell are you waiting for?