A bit off-topic to my usual posts about paper planners & bullet journaling, but today’s post is about how I plan and keep track of my investments in the share market.
I know there are online tools and apps you can use to track your shares, but most of them are a subscription. Yes I could tax deduct the cost of the subscription but it’s still quite expensive and I need somewhere where I can record my research. Most online tools only show you the data, I need a way to make sense of the data! I also wanted somewhere to record and track the goals for my portfolio.
There are so many shares on the ASX, let alone ETFs and international shares. It’s difficult to remember the data for all of the ones I’m interested in, plus how do I know the ones I’m interested in actually meet my investing criteria?
I’ve exported reports from my broker and websites such as Sharesight, but noticed errors in the exported data e.g. dividends for some stocks didn’t match my dividend statements, or some dividends I’d earned had been omitted from the exports. So I’d rather track it myself and know that the data is actually correct. I also like to update the spreadsheets progressively throughout the year, not just check my shares and export a report once a year at tax time.
So that’s why my spreadsheets are more focused on researching and analysing shares (making sure my portfolio is balanced and I’m meeting my investing goals), and compiling information for tax purposes (dividends, capital gains, franking credits etc.)
These are the spreadsheets I came up with!
To enlarge the screen of the video, click the square icon in the bottom right hand corner of the video (it will say ‘full screen’ when you hover your mouse over the icon).
If you’d like a copy of the spreadsheets, they’re available in my online store and Etsy shop.
There are 11 tabs included:
1. Helpful resources
2. My rules & research questions
3. 10 year summary
4. Annual summary
5. My dividends tracker
6. Share holdings
7. Shares watch list
8. International shares watch list
9. Share trading tracker
10. Share dividends tracker
11. Share price tracker
Input all of the websites, books, email subscriptions etc. that have information on shares which you trust / view regularly for advice.
As you can see I use various tools! Some of these are paid but if you sign up for a free trial you can usually access a fair amount of data and resources.
Some of my favorites are:
Big 4 Australian Banks – I use one of the big 4’s for their share platform and they’re my broker when buying shares. These share platforms have plenty of information for helping with your research. The one I use gives me a prediction of how much I’ll earn in dividends for the next 12 months, broken down by month and by share. I can download exports and reports on a certain date (e.g. end of financial year for tax time, or at any point in time during the year if I want to update my spreadsheets). I can download my share order confirmations whenever I want so I can find the HIN/SRN numbers needed to download dividend & franking credits data for tax time from share registries like Link, Computershare and Boardroom.
Simply Wall Street – I like their summary of predictions for each share and their snowflake tool. It also tells me if there have been big trades e.g. CEO offloading a large chunk of stock which may affect the share price
Sharesight – you can access their share checker tool for free. I like using this to see how a share has performed over the past year (how much $10,000 invested one year ago would be worth today, including capital growth and dividends). I don’t use the paid version of this to record my share holdings, as I already have this for free via my bank’s share platform, plus I can access most of Sharesight’s research tools on their free version.
I got a bit side tracked from my goals (dividend investor) during the buy now pay later / tech stocks hype during covid and lost some money. I invested in shares I wouldn’t have normally even considered, so I added this tab to refer back to and remind myself before buying shares.
I created some ‘rules’ and reasons why I have set that rule for myself. It may seem silly to write it down but it really is a helpful reminder when you’re making an assessment on whether to buy or sell shares in a certain company.
List out your research questions on this tab as well. Everything you’re going to investigate about the stock, so you can make an informed decision on whether or not you buy the stock. When you slow down and methodically analyse the data you can make an informed decision about which stocks to buy and are less likely to make mistakes.
Input your total dividends per annum and your total portfolio values for up to 10 years (goal & actual values). There are 2 automatically generated graphs to compare your goal & actual dividend values, as well as your goal & actual portfolio values.
There is also an automatic calculation so you can see how much passive income your dividends are creating for you per year, month & week.
See how your portfolio has performed each month, and how each sector in your portfolio has performed for the year. You can see what % of your portfolio each sector comprises, and if you are overweight or underweight in a sector.
You can track how much you’ve reinvested and how much your initial cash you have invested has increased over time.
There are pie graphs and bar graphs if you prefer to visually analyse the data. There is also a lessons learned section.
Use this to record the dividends you have earned on the shares you own. I send a copy of this tab to my accountant at tax time.
By keeping my records up to date as the year progresses, all the information’s already there come tax time. I know there are websites like Sharesight and broker’s where you can download an export of dividends however I found these to be inaccurate. I purchase all my shares through the same broker, yet some of the shares I’d earned dividends from, were not included in the exported spreadsheet from my broker (which is one of Australia’s big 4 banks). When I trailed Sharesight, some of the dividend amounts did not match my dividend statement from that company (and the amount that landed in my bank account).
I like online platforms that provide me with estimated dividends for the year but I need things to be 100% correct if it’s for tax purposes, so I prefer to track it myself.
List all of the shares you have purchased. You can choose to use a fresh version of this tab each financial year, or copy it over and keep a running total from when you first started buying shares, to the present day, on the one tab.
You can keep track of which shares have a dividend reinvestment plan, and if you have signed up for the dividend reinvestment plan.
I like to record the HRN / SIN numbers to make it easier to download dividend statements.
Add any of the shares you are researching in this spreadsheet. A good place to start when you are initially setting up your spreadsheets, is to downloaded a list of the top 50 (eg. ASX50) and analyse each share in this spreadsheet.
You can filter the ‘Buy (Hold)’ and ‘Buy (Trade)’ columns when you have some funds you’re ready to invest.
The cells in the spreadsheet will automatically turn blue if you rate a stock a 4 or a 5 to highlight that these are the shares you should buy next based on your chosen criteria.
Once you buy the shares you can delete the row with the share analysis from the spreadsheet OR if you know you want to buy more down the line when you have spare cash, you can keep it there, add a ‘Y’ for yes to the ‘stock up share?’ column, and just revise the value in the ‘$ I’m willing to put in’ column.
This is my favorite tab of the entire spreadsheet and the one I refer back to the most often.
This has the same layout as the ‘shares watch list’ tab however this one is for international shares.
I find it helpful if you’re starting to invest in overseas shares and / or want to compare the return on overseas shares with your local market.
I use this spreadsheet for researching ETF’s that invest in overseas shares.
In this tab, only enter the shares you’re intending to buy and sell (short term investments). You can change the tax rate depending on your country.
Use this for scenario planning (working out how much you need to invest and at what price you need to sell to make the desired profit). Don’t forget to update the brokerage value to the amount your broker charges.
Send a copy of this tab to your accountant at tax time so they can record your capital gain or loss.
I use Yahoo finance to quickly find the share price data. On their website, you can choose daily, weekly or monthly stock price. You can download an excel file with the data range of your choosing, then copy and paste it into the share price tracker tab of this spreadsheet.
I like to track the share price at the end of each quarter over a minimum 3 year period. You can change the timescales in the grey cells if you want.
The spreadsheet will then take that data and automatically calculate the average share price. I use this to help me decide what’s a ‘good’ price to buy the share for.
Use this to research each share on your watch list’s dividend history, to see if it’s a reliable dividend payer or not. The spreadsheet will also automatically calculate the dividend yield based on the current share price.
The cells in the dividend yield column will automatically turn light blue to tell you which shares are offering the highest dividend yield (which you may want to focus your attention on researching further before investing).
I started investing in the share market back in 2018, and have gradually built, tested and tweaked these spreadsheets over the past 5 years. I hope you found this tour of my share tracking spreadsheets helpful!
If you’d like a copy of the spreadsheets, they’re available in my online store and Etsy shop.
Found this post helpful? Pin it!
