How do you track your stocks and shares?!

[MortimerJazz (Morty)]

I’d certainly love to see the spreadsheets you’ve seen elsewhere - as well as link to those listed above!

Thanks for the topic!

[Mani]

Thanks to everyone who has shared possible solutions so far… what’s becoming more and more apparent is there isn’t a one size fits all solution. What I’m finding most frustrating is trying to convert these into GBP based currency spreadsheets, it’s such a pain! If anyone manages to turn any of these into a UK friendly spreadsheet please let me know!

Some of the ones I’ve found that I like but don’t really do everything I was after are:

Stock Portfolio Tracker in Google Docs Spreadsheet - Finance Train

r/investing - I'm sharing my Google Sheets Stock Tracker

The Best Free Stock Portfolio Tracking Spreadsheet using Google Drive

An Awesome (and Free) Investment Tracking Spreadsheet - The Dough Roller

Dividend Income Portfolio Template for Google Sheets

[Jimmy]

I use this template at the moment which works fairly well

Our Free Online Investment Stock Portfolio Tracking Spreadsheet

[LewisHarding]

‎Webull: Real-time Stocks

I’ve been using Webull for a while now and it’s pretty slick. It’s not as detailed as a spreadsheet of course but it works for me.

https://www.dividendmax.com/dividends/notifications

I also use dividend max to get notifications for my stocks.

[Mani]

I decided to have a go building my own simple spreadsheet and borrow ideas from the various options already out there. This is how far I’ve got… what do you guys think??

One of the things I’m noticing is that pulling data from Google Finance doesn’t always give you an accurate output, for example, when pulling share prices I have to divide the figure by 100 to get the correct share price in GBP to the penny. Also, unlike Yahoo Finance, the pricing information is 24hrs old.

[Mani]

I’ve given up my search for a spreadsheet. Some lessons learnt:

1. There aren’t any LSE / GBP / UK friendly spreadsheets out there - you’ll have to do a lot of re-work

2. You need to be comfortable that your spreadsheet probably won’t be 100% accurate at all times due to multiple sources of data

3. Google Sheets is probably the easiest solution to build your tracker but keep in mind that Google Finance data is reflective of the previous days prices (I haven’t confirmed this but it certainly seems to be the case), other platforms such as Yahoo Finance provides live data but pulling this into the spreadsheet requires a more advance user level (more complicated functions etc)

4. The best answer, in my opinion, is to use a purpose built platform or app. You do not need to incur a cost for this but if your portfolio is significant then a small cost shouldn’t detour you. The best I’ve come across seems to be Sharesight UK

I’m almost done building my own tracker, I just need to add categories and start building a few charts / graphs. If this doesn’t work for me then I’ll be seeking an app (like Sharesight) to manage my portfolio moving forward.

For a bit of fun, I’ve decided to use a colour scheme that reflects my investment platform of choice

[StockTricks]

do you invest in ‘GREGGS’ btw?

you should do !

nice pies.

[StockTricks]

@Mani

You need to be comfortable that your spreadsheet probably won’t be 100% accurate at all times due to multiple sources of data

Absolutely right… Google Sheets is sometimes behind in calling up market data, and doesn’t always have some UK stocks.

But here’s a Tip:
You can add the majority of UK stocks by adding the words ‘LON:’ in front of it.

For example, if you wanted to track the UK company ‘British Land’ it’s ticker symbol on the London Stock Exchange is LAND.

But because Google calls form US Markets, it will return data from ‘LAND’ regestierd on the NYSE as American Company, Gladstone Land.

So if you put LON:LAND. - it will return data for British Land.

Same with:

DGE
AZN
NG
BP

…if Google Sheets can’t find your stock, just try putting the words LON: in front of your ticker.

[Mani]

Hi @investgreg

Thanks for sharing that. I was wondering weather your buys and sells are entered in a separate tab and then appear in your main holdings list? or is it a manual count?

Have a look at a simple spreadsheet I’m in the process of building (almost done!). Perhaps we can trade some tips?

Thanks

[Mani]

I knew this would be a problem!!

Thanks for pointing that out @StockTricks… I think what I’ll do is add another column to select the currency or stock region

Then what I’ll do is add LON: in front of any ticker where I’ve selected UK as the region or GBP as the currency?? or am I missing something more obvious? Like just putting it in manually

My biggest frustration with Google Finance is the price data not being ‘live’, what’s the point of even offering this tool if the prices aren’t right?! Apparently they used to have a portfolio management feature built in some years ago but they’ve removed it

[StockTricks]

@Mani

…is the price data not being ‘live’, what’s the point of even offering this tool if the prices aren’t right?! A

Hey @Mani

So, it’s not really meant for you to make real-time decisions. Goldman Sachs wouldn’t be using this for multi-billion dollar fractional transactions to the milli-second.

It’s just used as a general quick and dirty overview of your portfolio. so it doens’t really matter if the data is late here or there.

If you’re looking for accurate critical live updates on price data, then Google Sheets is the wrong tool you are using.

[Mani]

@StockTricks you’re right. That’s why in my lessons learnt I mention that you’d have to be comfortable.

It’s a shame and annoying but I’m over it! The spreadsheet will do the job I’m looking for it to do. If I need something more advance I’ll definitely turn to an app of some sort.

p.s. I’ve sorted my spreadsheet to add whatever exchange I put in a separate column so now LAND would be searched as LON:LAND

[investgreg (Greg)]

I created a Google Form which asks for transaction data which it then saves in a sheet in the workbook. The individual pages for each person then only counts the transactions for that person.

It’s probably easier to show you.

A page of the form

A sample of the submissions

Part of the reason it asks which market is to ensure the Googlefinance function gets correct data. It uses that field to call the correct stock and also to convert dollar values into GBP for an accurate valuation.

It’s not the prettiest solution but it does the job for someone (like me!) who’s just playing around.

I like your one, especially the colour scheme (great minds!)

Regarding Google prices I’ve not seen them 24 hours out of date, at most around 20 minutes or so. Certainly good enough to get a rough idea of progress.

it all depends on what you’re looking for. I just wanted to have a better calculator about my own performance to assess my stock picks as I found the Freetrade app to lack in some areas there. I also wanted a way for my small group of friends to compare against each other in one place.

Your spreadsheet seems focused more on in-depth analysis around statistics of each stock and probably more professional!

[Mani]

Thanks for sharing that @investgreg much appreciated. I’m done with my one, I’m not going to make it anymore complicated! I really like the form you’ve used to capture the transactions and feed them into the spreadsheet, I’m going to go for something similar. Final touch will be a dashboard of sorts with all the visuals on it.

I’ve put submitted an suggestion in the ideas category to add portfolio trackers to the app, if anyone reading this is up for it please head over and give it a vote!

[StockTricks]

Does anyone know how to get the Dividend Yield on Google Sheets?

[Mani]

I used an XML code that pulls it from Yahoo Finance, do you fancy using that?

[Pdw (Paul)]

@investgreg Is this something you are able to share?

[investgreg (Greg)]

I tried to have a go but it’s a lot of work to remove all the personal detail for the other people, time I don’t have right now I’m afraid.

I’m more than happy to share my info (as you can see above) but don’t have their permission to share and it’s so interlinked that I can’t just do a simple delete on a few parts.

If there are any particular formulas or ideas you’d like more information on I’d be happy to help as much as possible.

[clemlanoe (Clement Lanoe-Chaumet)]

Would you be willing to share a template of that spreadsheet? Looks great

[finki]

@StockTricks

Does anyone know how to get the Dividend Yield on Google Sheets?

Sure. One of the most popular functions on the finkiAPI is “ukDividendYield”. It does what it says on the tin. A T212 user made a video and included it

Trading 212 Dividend Portfolio Finki API + £80 $100 month Passive Income