Google Sheets Portfolio Tracker

Afternoon all,

Thought I would share a thread on this. I have built a new portfolio tracker using the exportable data from the 212 app in Google Sheets, however I am still tweaking some of the setup.

*** Edit - current version ***

*** / Edit ***

High level, this currently works like the following

  • 0. Setup - Basic setup parameters, mapping of 212 export tickers to GoogleFinance ticker codes, and the base currency of your portfolio to report in.
  • 1. 212 Trade Export
  • 2. 212 Holdings - a table of all unique holdings, 212/Google Tickers, details if the security prices in the major/minor currency.
  • 3. Exchange rates - to convert your holdings back to portfolio base currency.
  • 4. Security Prices - day by day prices.
  • 5. Security Positions - a day by day holding report.
  • 6. Bookcost - this is done on an average cost basis, and currently excludes dividend impact.
  • 7. Security Valuation - End of day on day valuation of your security positions.
  • 8. Return Analysis - Provides a simplistic return of current value less bookcost, but also unitises all cash deposits/withdrawals, so you can directly compare your performance against another Fund/ETF. I am currently using VWRL, but get this is an income ETF, so probably want to pick a longer serving accumulation fund.

All in all, I’m about 70% of the way there to having V1.0 ready to go, and will share a sort of draft once I am happy with this, as would be good to get input from others.

The main issues I have found are:

  1. Mapping in the Trading 212 ISIN/Ticker to the appropriate GoogleFinance code.
    current solution is an override on the 0. Setup tab. If anyone had a list of these, or if there was a way we could gather these collectively over time - perhaps a google form to update the template, that would be awesome! Think of thinks like all the free NG(National Grid) shares people have received, needs LON:NG to pull in the correct pricing data.

  2. GoogleFinance calls - Filters can be used to refresh the appropriate columns/rows for the amount of user data provided. Unfortunately I think each security needs its own GoogleFinance call, and this formula needs to be fairly static on the sheets, so I have currently set it up to deal with up to 100 securities. For more the formula needs extended. A google script might be an option when a refresh button is used to expand/contract columns and rows as required.

16 Likes

I’ll be absolutely no help here as it’s beyond my skill set.

Just wanted to say thank you for your efforts!

3 Likes

i got this from someone else (cant remember where, apologies)
might be helpful?

look forward to your version! thanks in advance :smiley:

4 Likes

I think this would be addressed if the CSV contained the exchange as a separate column.

This sounds great Dougal!

I second Jobloggs’ comment.
If you do manage to get something running and are happy to share then that would be incredible. :smiley:

I think that link should work - note its a draft :slight_smile:

Theres a few things that need done.

  1. Exchange rates needs fixed to work with any currency pair.
  2. Security Pricing needs updated as it assumes all markets have the same holidays.
  3. Security Positions - I would prefer this worked with a query to complete the table as stocks are purchased.
    6 Average Cost - same as 5.
    7 Security Valuation - happy with this as long as format of 3-7 remains consistent.
    8 Return Analysis - fairly basic for the moment.
2 Likes

regarding issue 1, a list of all stocks on T212 is here with the values you need (ticker, ISIN, exchange)

note: this was produced by finki, about a month ago in this thread…

you should be able to just concatenate the exchange with the ticker to get the google finance input, (eg: Map London Stock Exchange to LON, then concatenate to :NG to get LON:NG) This spreadsheet mapping will get outdated however and how to keep this up to date would be another issue…

1 Like

Thanks Trader787 that looks really useful. I’m giving up for tonight, I might have a look back later and see if there are any helpful comments, and will see about adding that in.

The next focus I think is to get it to work for any currency pairing, and then resolve the issue with different markets having different bank holidays. That should be fairly straight forward.

I would also like to remove the need to use Sumifs as much as possible, queries/arrays expand much better and should be much more efficient.

can’t you just use =GoogleFinance(“CURRENCY:USDEUR”) to get any exchange rate between any pairs you want? or do you need more than that?

bank holidays might be a challenge, why do you need that?

That could work, but lets say you want 5 years of exchange rates/prices.

You would need to have approximately 5x252 or roughly 1260 rows of Googlefinance calls to get the exchange rate for each and every day over the period.

OR you could use the following formula

=GoogleFinance(“Currency:USDEUR”,“PRICE”,From date, to date), and get all the data in one go.

Just to let you know, I’ve had good success with using your template so far. Thanks for all your work on it.

The area I’m particularly interested in is being able to see the makeup of my portfolio over time. For example, I’ve graphed the security valuations sheet to create this;

It might be too much to ask of a single chart, but I’m aiming to be able to see change in notional return per stock over time. It would be nice to be able to look back and see at a glance which stocks have had particularly good and bad days. Maybe my first step would be to filter my holdings to only show stocks that have had a daily change in return of above 10% or below -10%.

1 Like

Thats an interesting thought. so take the current valuation less bookcost, and graph the unrealised over time?

I also think bookcost for the security should be adjusted - reduced by the value of dividends to reflect total return.

I still want to fix the exchange rate input to work with multiple pairings, and will have a look into it once done as shouldn’t take too long.

Hmm, yeah that’s close to what I was imaging. However it shows cumulative growth per stock over time, whereas I think I’m looking to plot day-on-day change in unrealised returns over time (i.e. a reduction in unrealised gains day-on-day would be plotted as a negative value, even if the overall unrealised gains are still positive). Below is plot of unrealised over time;

What I think I’ll try is duplicating this new table of current valuation less bookcost, but instead calculate the change in unrealised gains versus the previous day; for each stock and date. I’ll try that now and let you know how I get on :slight_smile:

1 Like

Damn, I’ve tried for a bit to visualise what I had in my head but it looks like shite haha. This is a plot of the change in unrealised returns per stock per day (i.e. a stock that tends to be positive is growing, a stock that tends to be negative is dying):

As I suspected, its too much to ask of a single graph to try to show change in return per stock per day when you hold over 50 stocks. Its a little clearer if you limit it to just one stock, like this.

I suppose this kind of graph is a measure of the volatility of the stock? Pretty new to all of this terminology so might be wrong. Even with just one stock, it isn’t very easy to see the cumulative impact of consecutive positive days. So I think my previous graph (in the previous post) is actually more useful. Nevermind, nothing ventured, nothing gained.

I like the second one, I’ll add a graph with a filter on the stock.

Not entirely sure how much ‘volatility’ helps though, can be such a thing as too much information.

1 Like

I think the overall problem I have is that I have a wide range of stocks, so when my overall unrealised returns suddenly drop on the dashboard of the trading 212 app, I find it tricky to work out whether its due to a single stock plummeting or the cumulation of minor drops in value across a range of stocks. I was hoping these visualisations would help me find the culprits.

Maaybe, what I actually need to do is have a dedicated sheet showing change in unrealised gains per stock since the market last opened. Then simple conditional formatting to help me see at a glance how well the stocks are currently performing. That’ll be my project for tomorrow night!

Change in unrealised gains would be impacted by buys and sells.

You almost want a top 5 movers, but I think it’s the change in unrealised on sales that might remove what your after.

Little more progress, I’ve pulled 7 day change in unrealised gains, sorted by amount and filtered out values close to zero:
image
So this tells me which stocks have had the greatest impact on the value of my portfolio over the past week, which is really handy. No idea how buys and sells would impact this, will have to experiment some more. It’s not a massive problem for me personally as I’m mostly a stock hoarder and rarely sell.

I created my Google Sheet portfolio tracker some years now. No issues at all and working perfectly:

What I think would be amazing is to have the possibility of exporting your portfolio with info such as buy price in origin and target currency. That’s the main issue I am facing currently.

2 Likes

Hi I am also looking to build a Google sheet for Portfolio management. Is it possible to integrated Google sheet with Trading212 ?

Means if we buy a new stock or Buy/ Sell from an existing portfolio in T212 will this updated into Google sheet automatically? Im doing this manually at the moment.

Thanks