How do you track your stocks and shares?!

Hi all,

I’m fairly new to investing and I’ve really been enjoying my journey so far with Freetrade.

Just out of interest - I’ve seen, both on YouTube and on various sites, that some investors track their stocks and shares (inc. dividends) on fancy spreadsheets so I’m curious to know if anyone reading this uses one and which you would recommend?!

I’ve found half a dozen decent looking spreadsheets which I’m happy to share links for here but I just wondered if there was a “go to” for most people??

Thanks!!

1 Like

This blogger has built a frankly amazing template spreadsheet you can use:

Thing is you’ll have to mess with it a bit to get it into GBP, but it’s a great start.

4 Likes

I made a quick thing on Google Sheets which has transformed itself into something a bit more complicated over time. Google Sheets has a very useful function to call live prices and share information from Google Finance so it automatically updates everything without me having to do anything.

I use it with a couple of friends to compare performance as we play around and learn.

All I need to do is add an update everytime there is a buy or sell.

Summary Screen

Individual

(Please don’t use this as portfolio advice, as you can see it’s not going swimmingly, that’s why I’m learning with small amounts first!)

9 Likes

The GOOGLEFINANCE function in Google Sheets is great because you can do pretty much anything you want with it.

Have a look at this topic:

1 Like

Haven’t used it yet, but stumbled across portfolio123.com this morning.

Wil give it a go.

1 Like

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

Thanks for the topic!

1 Like

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:

1 Like

I use this template at the moment which works fairly well

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.

3 Likes

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.

3 Likes

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 :wink:

5 Likes

do you invest in ‘GREGGS’ btw?

you should do !

nice pies.

1 Like

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.
Screenshot 2020-03-29 at 16.50.43

1 Like

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

I knew this would be a problem!! :man_facepalming:

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 :joy:

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 :roll_eyes:

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.

@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 :+1:

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!

4 Likes

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!

1 Like

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