Hi,
I know this is an old thread but I love the look of your tracker.
Would it be rude to ask for a template?
Thanks
C
1 Like
What do you use to record your shares and share dealings? An app or your own spreadsheet?
1 Like
Unfortunately the app doesn’t give me all the information I need so I have to keep a spreadsheet as well. For example, dividend history and ex-dividend dates.
it is hard to know what to keep on a spreed sheet and takes a long time to fill in all the details
I track my portfolio on excel (laptop) and stockevents (phone app).
Stockevents is good, you can track your entire portfolio on there and it creates a calendar list of upcoming events (earning history, ex-dividend, dividend payments and how much to expect based on shares etc etc).
Stockevents is free. Only downside is you can only add a certain amount of stocks into your portfolio. I got around this though by recommending friends download the app (+5 stocks per recommendation). Friends just need to download the app through your referral and then they can delete.
I use Excel for recording my buys and sells…
1 Like
I use Googlesheets.
4 Likes
I still maintain a spreadsheet which originally was used to provide some high level stats on my portfolio but it’s purpose these days is more so to record Order ID’s and transaction details should any of my brokers have an IT disaster!
I track all my shares in SimplyWallSt, as this keeps track of dividend income. Once a month I export it to CSV and link it to a dashboard spreadsheet I created, for real in depth info.
1 Like
Hi all. Not sure where to post this request but does anyone have a tracking spreadsheet they can share ? to track stocks and dividends and which pulls share/dividend pricing from the web if possible. Thanks.
Hi Guys,
Putting together a spreadsheet for my stocks. Using a template which can be found here for anyone interested. (Not my template)
Though the formatting for the sheet is in usd, I need some to be in GBP.
With the formatting of share price in the uk being different from US stocks, when the price is carried in from google finance, it is brought in incorrectly. E.g LON:CRS is 1088p (£10.88) but the spreadsheet assumes US format and price of £1,088. The cells are also formatted to $. Can someone advise on the commands to change the format to £ if the ticker contains “LON” along with altering the decimal point to adjust?
If any one has any nice google sheets for their portfolio and/or DD that they wouldn’t mind sharing a template, it would also be great!
4 Likes
Assuming you have a symbol in cell A2, then
- Get exchange rate using =GOOGLEFINANCE(“CURRENCY:USDGBP”) and store in some cell, for example C1
- Get currency for your stock using
=GOOGLEFINANCE(A2, "currency")and store it in B2 - And then
=GOOGLEFINANCE(A2)*IF(B2="USD", $C$1, IF(B2="GBX", 0.01, 1))in whichever cell you want your price in GBP to be.
You can do it all in one formula in one cell. But I think it is faster if you reduce number of calls to GOOGLEFINANCE, hence using cells to store intermediate.
6 Likes
Hi @nickab
I’m having great difficulty getting these formulas into the into existing document, prpbably because I don’t know how to insert formulas into the correct ()'s . Would you be able to shed some light on this or even update one row in the google sheets doc and share it, if you had time. I know these things take seconds when you actually know what you’re doing, however I don’t 
Can I pm you a link to a Test Google Spreadsheet to add the formulas?
Cheers
Kelv
Try Add formulas & functions - Computer - Google Docs Editors Help first.
If it doesn’t work, PM me a link.
Notes:
- You might need to add new columns and change cell references that I used, like A2, B2, C1, based on where the data is in your spreadsheet.
- $C$1 – is fixed/absolute reference to C1 cell, i.e. when you copy your formula to the next row it will still reference C1 (where in my example, we have currency exchange rate).
Hi Nick,
Thanks for taking the time to look over this.
I think the issue myself and the OP are having is that we would like the UK stocks to feature in the spreadsheet in their native GBP as what’s happening currently is it’s showing false values in Column E with the way it shows the incorrect decimal point placing because it’s using the Amercian format.
We don’t want to actually convert US Dollar stocks such as AAPL to £’s. - CBX, CCL and RMG are all £ UK stocks.
I guess this is correct formula that you’ve put on the sheet, *And then =GOOGLEFINANCE(A2)IF(B2=“USD”, $C$1, IF(B2=“GBPX”, 0.01, 1)) in whichever cell you want your price in GBP to be. but I don’t know where to put it in to the existing formula in column E.
Ta
K
Decimal point is correct if you take into account stock’s “currency”. Some of the UK stock is in pence sterling (GBX), not pounds sterling (GBP). If you want all UK stocks to be in GBP, the general solution would be something like (assuming A1 is ticker):
=GOOGLEFINANCE(A1, "price")/IF(GOOGLEFINANCE(A1, "currency")="GBX", 100, 1)
Specifically for your sheet, (assuming row 7) you would replace formula in cell I7 from
=IF(ISBLANK(A7),,GOOGLEFINANCE(A7,"price"))
to
=IF(ISBLANK(A7),,GOOGLEFINANCE(A7, "price")/IF(GOOGLEFINANCE(A7, "currency")="GBX", 100, 1))
This will normalise any GBX stock prices to GBP. I’d also suggest replacing number formatting, currently it uses dollar sign, which will be confusing if you want to keep stocks in their native currency. Format -> Number -> Financial or custom number format to increase number of decimal places.
1 Like
There was a typo in my initial post. It should be GBX, not GBPX.
Ah, I was unaware what GBX was, so that’s making sense now.
Appreciate this very much, Thanks
Someone asked about what people use for tracking so I figured I’d post my sheet. I’ve eliminated my holdings, but it is still populated with my share picks as a starting point in how to use it. I’ve added in usage comments, but hopefully it should be pretty straightforward.
In essence add in share purchased to the yellow lines and dividends on the green lines according to the correct months. I went ahead and added in prospective information for the year and then correct it when better info is available nearer the ex dividend date. It then tracks the purchase price and avg price paid and gives a yearly % yield for the dividend and also for the capital.
I’d appreciate it if you kept this one empty and made a copy into your own space for editing.
2 Likes
Hi everyone!
New to the FT community today so hello all!
I am wondering if anyone has any good software for tracking dividend payments? I currently do it manually through excel and will explain my template below. Just wondering if there is a software package that could do this for me to save time.
I am predominantly a dividend investor so like to know how much each of my individual stocks has paid me in:
- Total payment per stock
- Payment relative to total investment per stock
I also like to track monthly dividend payments so that I can see that my monthly dividend payments are gradually growing using a 3 month moving average (for all stocks combined).
The dividend tracking system in FT is lacking. That is okay as there are bigger priorities for FT.
My excel template has the following:
-Ticker
-Dividend Yield
-Total investment
-Market value
-Percent increase (market value - total investment) / total investment
-Percentage of portfolio (stock value / total portfolio)
-Expected yearly dividend payment (Total investment x dividend yield)
-Total dividend payment (sum of the dividend payment per stock - I track this using monthly columns)
-Dividend payment relative to investment (total dividend payment / total investment)
This then pulls through to a bar graph showing:
- Total dividend payment per stock
- Total dividend payment relative to investment per stock
The goal is to quickly track those that have paid me handsomely compared to initial investment, and then I drip funds back into them (keeping the total dividend payment relative to investment below 5%).
The monthly dividend payment columns for all stocks go through to a bar graph with a line of best fit and a 3 month moving average. The goal here is to have a positive correlation over time.
The visual aspect is great, providing reassurance that my portfolio is moving in the right direction. It is hard to track dividends through emails and the activity tab FT provide.
Does anyone have decent dividend tracker software that makes this easier? Or is it a case of excel it for now?