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:
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.
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.
Exchange rates needs fixed to work with any currency pair.
Security Pricing needs updated as it assumes all markets have the same holidays.
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.
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ā¦
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.
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%.
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
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 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!
Little more progress, Iāve pulled 7 day change in unrealised gains, sorted by amount and filtered out values close to zero:
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.
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.
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.
