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.