Using Google sheets and Data Studio to track a share portfolio
During the pandemic share trading has become more popular. With all the meme stocks, bull market and money printing, the stock market has actually been a great place to invest your money. Whether at the time of writing in October 2021 that still holds is an open question.
The conventional wisdom nowadays is that you cannot beat the market so for most people investing in index funds is the best way to play the stock market. This is probably true for most people in the world. However, in Ireland they have something called exit tax and deemed disposal. Exit tax is 41% on ETFs and every 8 years you have to pay tax on them as if you had some them, even if you didn't. This hobbles their ability to compound. Share picking or buying investments trusts is far superior post tax.
I do have a small pension in Ireland. Basically, you can set up something called a non standard PRSI and its like a SIPP in the UK where you can get your pension money and invest in what you want. ETFs in a pension like that are fine as they are tax free until you start drawing down.
So in the UK, I do have a SIPP and its invested in the stock market. I used to buy low cost tracker funds but decided that I could make more by doing a little research a ‘betting' on my skills. Its easy in a bull market as everything is going up and everyone thinks they are Warren Buffet.
Google Sheets
You can use Google Sheets and the finance functions to track your shares. My SIPP broker is Interactive Investor in the UK. They allow you to download a handy CSV of your holding which you can upload to Google sheets.
You need to do a bit of editing but once you have the symbols in a way Google understands, its straight forward enough to pull in live prices.
The functions you need are simply
=GoogleFinance($A2,”price”) where A2 is where your stock symbol is.
=GoogleFinance($A2,”change”) to give you the change in price from the opening
=GOOGLEFINANCE(“Currency:USDGBP”) – to give you the USD to GBP exchange rate if you are buying USA stocks.
The other thing you might need is to be able to value funds. Google finance doesn't pull this information in so you will need to scrap it from the web. I did some Googling and found this function
=value(SUBSTITUTE(query(IMPORTXML(“https://www.hl.co.uk/funds/fund-discounts,-prices–and–factsheets/search-results/v/vanguard-us-equity-index-accumulation”,”//span[@class='bid price-divide']”),”select* limit 1″,0),”p”,””,1))
It just scrapes the price from the Hargreaves Lansdown website.
Once you have that, its possible to work out all your portfolio, do all the calculations in sheets and draw pretty charts in sheets but Data Studio is a lot better for this.
Data Studio
So you just simply connect your sheet to Data Studio and add the charts.
You can see my portfolio is heavily concentrated in Watkin Jones and tech stocks, mainly Google shares. Tech is going to be powering most indices so I figure I may as well invest in those directly rather than funds. Watkin Jones is a build to rent provider that should do well in the UK. This was a tip I got off a professional gambler I follow on Twitter.
This will update automatically now and so I won't need to be logging in to ii.co.uk to check my investment performance. I really shouldn't be doing that anyway as I have many years before I can touch this money but its so hard not to keep checking in a bull market. I think now its potentially a bear or flat market, the temptations have gone.
Ben has a BEng (Hons) in Computer Science and 20 years of experience in online marketing, specialising in SEO, lead generation and affiliate marketing. After spending over a decade as an igaming affiliate, he has decided to concentrate on GA4 training and SEO Audits.
