I canāt see the option for the BETA website version aswellā¦ tried on a fresh icognito page aswell but still canāt see any optionā¦ Can you guys confirm at the moment this is only on mobile? (it works there)
Good information thanks for sharing
Fieldengineer
Im v happy with the new export feature. Any tips how to import the csv file into Yahoo Finance or some other preferably low cost or free portfolio management system? I need to track my profit/loss for tax reporting. Iām not IT-savvyš„ŗ
thanks for adding the ticker column. It would be awesome if we algo got some columns with pie data (if the order what from a rebalance, or not from a pieā¦)
How did you sort everything? When I export to Microsoft office I get a huge mess.
Where can I find the download data from the website? I can only see this option on my iPhone. Thanks
Itās not yet available on desktop site. But you can do things like: download on phone, open with Google sheets app, save to Google Drive cloud storage, and then open from there on desktop.
Anyone have much luck parsing the data into useful pivot tables and charts in google sheets?
So far Iāve managed to pull current stock prices using:
=GOOGLEFINANCE(āticker nameā,āpriceā)
and apply currency conversions using:
=GOOGLEFINANCE(āCurrency:USDGBPā) (for example)
The result of which allows me to pull together a simple graph showing current return on my investments;
Note: Iāve intentionally obscured the graph above to not reveal all of my investments and returns
Couple of significant limitations/challenges Iāve come across so far are more related to google sheets than anything else:
- Google sheets doesnāt seem to recognise GBX as a currency, so you need to build in manual checks for this currency and apply a conversion
- The currency and value that Google Sheets lists for some stocks doesnāt seem to match Trading212. Maybe someone with more investment experience can shed some light on this. For example, Renault (RNO) is listed at ~33 euro per share on Trading212, but when you use =GOOGLEFINANCE(āRNOā,āpriceā) its listed as 14.5GBX.
I think google sheets can pull historic stock prices, so my next project is to explore displaying return values over time.
An area Iām struggling with is on sold stocks. Because of how the data is displayed in the CSV, the default output when you try to take into account both buys and sells doesnāt quite work.
For example, I buy Ā£10 worth of stock, then sell it for Ā£8. Its difficult to extrapolate from these two values that youāve lost Ā£2. For the calculation to lead to negative Ā£2, you would need to consider the Ā£10 as negative and the Ā£8 as positive. But doing this leads to all of your bought stocks counting negatively towards your portfolio value. Still trying to figure this outā¦
Try =GOOGLEFINANCE(āepa:rnoā)
This stock trades on Paris exchange.
You can use IF to distinguish Buy and sell.
=IF(a1==āMarket buyā || a1==āLimit buyā , f1, - f1)
Ah I see, thanks! In that case, I have a request for the CSV to include the exchange that the trade was completed on (@George). So that this can be factored in when looking up current stock prices using the =GOOGLEFINANCE(ātickerā) formula.
That bit I can do, what I donāt understand is how to get from that to a value that shows whether you have gained or lost for that particular stock.
I suggest you create a lookup table with a column of tickers against a column of Google reference names for all the stocks you follow. Then something like
=VLOOKUP(A1, A200:B300, 2, FALSE)
Try the function SUMIF to sum cost of purchases and sum proceeds of sales.
I canāt see the option on how to do this on the web app? Legacy or new. Is this ready yet or am I just overlooking the setting?
Quick update. Iāve got to the stage where I can automatically show percentage change in value over time for each of the stocks I own (see below for example);
Next step is to tie this in with the purchase and sell dates/values from the exported CSV file. Hoping I can get to the stage where I have a single stacked chart showcasing the value of my portfolio over time while easily revealing the stocks having the greatest and weakest impact.
Yeah, thatās what Iām aiming for. But it gets super complicated (unless Iām missing something obvious).
For example;
- you buy 2 stocks for Ā£20 (Ā£10 each)
- next day they drop in value to Ā£5 each
- you sell 1 of them for Ā£5 (making a loss of Ā£5 on that 1 stock)
- youāre left with 1 stock worth Ā£5 that you paid Ā£10 for, plus youāre Ā£5 out of pocket, so youāre down Ā£10 overall
I donāt think a simple sum of the purchases (Ā£20) and sales (Ā£5) can give you the full picture, the formula needs to consider the stock value and consider both realised gains and unrealised gains.
I think Iām going to be wrestling with this for a while to figure out how to meaningfully represent the value of my portfolio within google sheets (and donāt get me started on trying to factor dividends into the whole thing haha).
I also posted on the wallmine thread, as its getting too fidgety to setup. The issue is more on the Wallmine side, than the data content from the 212 extract.
I am going to attempt finishing something along the lines of the below next week in google sheets with the new 212 export:
- Tab 1: 212 import
- Tab 2: Table of all the individual stocks ever held, current position, first purchase date, last purchase date.
- Tab 3: A download of all the prices needed from Tab 2 to value securities between required dates. It might need a lock in/reload function to remove formula to speed up spreadsheet.
- Tab 4: A breakdown of all units held per stock on a day by day basis.
- Tab 5: A breakdown of the bookcost of each stock held per day (Purchase less sales less dividends).
- Tab 6: A breakdown of the current valuation of each stock.
- Tab 7: tab 6 less 5 figures.
- Tab 8: Graph of performance summary over time - Total Return / unrealised / realised / dividends to date.
- Tab 9: Ability to change filter to view all google finance stock history information.
Tabs 2 to 8 should auto update.
I will look to include a means to compare against 5 benchmarks potentially as well.
Iām not sure if it will ever be simple to break down performance by āpiesā, especially if the same stock is held over multiple pies when you import your trading history.
If anyone is interested, or has some ideas how to improve before I start, feel free to give me a PM.
will you be sharing a link here?
Yes - I made a placeholder thread for feedback
Just tweaking a few things I donāt like first.
Im asking again.
PLease fix Cameco Dividend. My exported file shows withholding tax: ā-0.00ā which is incorrect. I received 0,053$ per share. However the dividend āpre-taxā was 0,0615$ per share if i remember correctly.
PLease fix this asap as i need to pay my taxes soonā¦
Kind regards
Just saw the new export feature. Sweet
One feature that could be added (unless I didnāt see it), would be the ability to quickly choose a generic time frame like ālast yearā ācurrent yearā āYTDā ālast monthā etcā¦
But thatās nothing of importance for now really.
Good job @George @Team212 !