HairyTurtle is a domain name gifted to me as a birthday gift. I decided to create an online share trading capital gain tax (CGT) calculator applicable to Australian share traders due to the complexity around the accounting rules. This post explains the origin and how I designed the solution. There are clearer instructions on the page itself.
My journey to share trading
There are many things in life that I’m grateful for, and one of them is getting to know some of my closest friends who taught me how to invest right after I graduated. We talked about property investments, share trading and financial strategies we can deploy to achieve financial freedom earlier.
Share trading was particularly intriguing because of the liquidity and the element of betting. I admit some of the early trades were super risky and naive, but the amounts were always little, trading only what I owned and ready to lose it all. I’d argue that it was the loss of money that taught me the value of consistency and discipline in the world of investments. It opened up a world of possibilities to me and lulled me into further investigations on business valuations and technical analysis that was never taught at school. (Robinhood making share trading accessible to “prey” on young adults does the same thing albeit granting option trading a lot earlier than necessary, but I digress.)
One of the side-effect of trading shares is that you have to deal with the paperwork of capital gain tax (CGT) for every year’s tax return. It was initially easy, since all shares were bought and sold within the year. Therefore, the loss is carried forward for profit deduction the following years, while the profit is taxed immediately.
However, as I dug deeper into company valuation and portfolio management techniques, I started forming my own investment strategies which I tried to follow as closely as possible. Over time, my share trading habits progressed into more of a long-term investment exercise where roughly 70% of my shares are held mid- to long-term (5 to 10 years, reviewed every year), while the rest are traded based on technical analysis and shares momentum.
This created a messy situation because some shares sold are eligible for the CGT discount while some are not. The rule is pretty simple:
If the lot of shares you sold were held more than 365 days, that lot is eligible for a 50% profit tax discount.
But think about this following scenario: You bought 100 units of shares on the first day of every month starting in January for 13 months, and then sold 450 units on the first day of the 14th month (February next year, which ATO calls a “CGT event”). How many units of the 450 you sold are eligible for the 50% discount? If you say 100/450 units, then you are using the first-in first-out (FIFO) strategy, which is not wrong, but what if the first lot of 100 units were bought at a very low price (January price: $0.5) compared to the peak on the 3rd month (March price: $2)? Wouldn’t you sell the March 100 lot before the January lot to minimize tax you have to pay up-front? Picking-and-choosing lots to sell is allowed under ATO’s rules as long as you keep track of which lots were sold and which are still held.
This is the complexity that came with such a simple rule, all in the name of encouraging long-term investment and penalizing short-term trading.
Sorting through trades can quickly become very cumbersome, especially since
- You don’t always sell the whole lot of shares you have, as most ATO examples tend to demonstrate
- Different lots has different pricing, and selling shares may involve selling multiple lots of different prices
- Brokerage fees and other investment borrowing costs are excluded from the CGT discount
To sort through hundreds of trades by hand (I really mean Excel) usually takes hours on end. To make things worse, the same process needs to be repeated by your accountant since one of us is really just repeating the work to confirm that there is no error in the calculations. There was a year when I made a mistake and ended up re-doing the analysis a second time, wasting in total a few brain-powered units of days.
Your accountant if most likely going to be using FIFO strategy to keep track of the shares you hold and sold, because it’s the simplest. However, I can tell you straight up that it’s not the best strategy to have as I can attest to having thousands of dollars being returned if the trades are sorted using the optimized method to minimize the profit that needs to be taxed.
Hunt for a solution
So what does the ATO say about this? There are a lot of information available on the ATO website, but also a lot of important cases not covered by the example scenarios. I had to hunt around for a tool that will help me calculate the CGT easily. ShareSight came very close to solving my problem, except that it’s a paid app for a tool I only use once a year since I have other ways of tracking my portfolio. It is also limited to a number of shares per portfolio which defeats the purpose.
ATO’s own calculator is even worse. It allows you to add one historical trade at a time, which is clunky and slow. What I need is a tool that accepts a common broker’s exported trade confirmation, upload and be done with it.
Of course, it’s not always that easy. When I decided to implement my own calculator, I came across so many edge cases which needs to be handled, which makes it a lot less likely that the ATO will implement a calculator that suits every use-cases out there. For example, I have participated in a few Share Purchase Plan (SPP) before, a capital raising activity that allocates additional shares to existing shareholders at a discounted price without the brokerage fee. These events won’t be recorded in the broker platform’s exported file. Other edge cases include stock split or consolidation which happened to me once. My early days gung-ho trades also meant there were some companies that went completely under, with the ticker symbol removed due to a merger & acquisition. Another had its ticker changed to have a fresh facade and branding. Some better companies granted bonus share issues, and some were converted in the Dividend Reinvestment Plans (DRP). The list goes on, and I am sure these wouldn’t be the exhaustive events that might happen but not available in the broker’s platform.
Reading this specific example on ATO’s website, I set out to put things into my own hands to automate the CGT calculation once and for all.
Solution mode
I wanted a calculator that can be reused every year, is easy to use, unit tested for validity and abides by the Income Tax Assessment Act 1997. I started with Excel macros since most trades are exported into CSV anyway, and Excel is a very user-friendly tool in the accounting world.
Granted, I’m not a huge fan of Excel VBA. But I was quickly met with limitations and constraints that I could not get past. Objects lifecycle, deeply-branched loops and unit tests are difficult to achieve without many intermediate hidden layers of data transformation being output to some sheets for further analyses. It’s simply not the tool to solve this problem. So I moved to using Python.
HairyTurtle’s alpha version had just a CLI to pass in arguments for trade analysis. It ingests all entries and looks at your historical units held to make the decision based on the mode selected: FIFO, FILO, Minimize Loss or Minimize Profit (Optimized). I then added the feature to allow adjustments based on the information manually gathered from various places. The adjustments are to capture special events that are not known by your trading platform as explained above. These special events are normally available through email communications or market registry service providers such as ComputerShare and Link Market Services. The adjustment entries are similar to trade confirmations except they are construed as a different event, with an atypical brokerage fee and prices.
With the help of unit tests to confirm my code validity, I also realized the adjustment entries themselves can be lacking in usefulness because you may miss some special events that might have occurred during the year. Since my unit tests already passed in a final portfolio that you can easily get from the trading platform, I thought I might add in an additional field where users can upload the “expected” EOFY portfolio for the calculator to compare against. If there are stocks that are not calculated exactly as expected, then trades for these ticker symbols are placed into an ignored file for further analysis by the user.
Results
The tool worked well for me. What used to take a few days of sieving through Excel entries is now done in just a few minutes. Regardless of whether one uses the FIFO or the Optimized strategy, this tool would’ve impacted the efficiency in preparing your tax return document.
So, which strategy came out better?
Back when HairyTurtle was first written a few years ago, I ran the code through all historical trades to compare the tax I have paid, and the tax I would’ve paid if I had used the Optimized strategy from the get-go. Turns out, I overpaid tax for roughly $21,000 of the profit made over 3 years, which translated to a lot of thousands of additional tax paid because it was too difficult to sort through algorithmically to optimize CGT. FIFO is widely used for a reason because it simplifies record-keeping and tracking the lots of shares still held, but this does not mean the Optimized way is not allowed. It is, after all, an example provided by ATO.
After consulting a few accountants in my circle of friends, I got the green light to proceed with this technique and decided to amend all my previous years’ tax return. With the help of my accountant, the once-off amendment was done to transition to this new and optimized way of calculating CGT. Since the tool also spits out the lots of shares still held in the portfolio, with details as to when and at what price the lots were purchased, I can easily satisfy ATO’s book-keeping requirement. I got back some additional tax paid, saved valuable EOFY tax prep time and reduced my accountant’s workload. Win-win-win!
Making it more accessible
The most bizzare thing through the process of first wasting huge amount of time, to building my own tool to solve the problem, was that I could not find a tool that actually suited my need. In the digitized world we live in today, I could not imagine this happening. Or maybe I just had to admit that my Googling skills is not quite up-to-scratch as I thought. But part of me actually wanted this solved by myself for a sense of achievement, since coding and learning is part of the fun.
In order to make this tool accessible for my friends and accountant, I have thought about making this code open-sourced, but there are risks involved that I am unwilling to take on, and not everyone is a coder. So I am sharing this tool only if I know the user will perform his/her due diligence to double check the outcome.
To make it easy to use, I initially created a Python Tkinter GUI to run the CLI which worked just as well. However, it has always been my interest to learn a bit more of web designs and strengthen my cloud technology understanding, so why don’t I just do it on my website?
Putting it all together
Understanding different pieces of the solution hangs together is not critical to using the tool, so the following information are as much to feed the curiosity of the techies out there as it is for my own documentation.
Since I host my website on wordpress anyway, I decided to embed this tool in a form that is accessible on the HairyTurtle page. This following diagram describes the different parts of the solution.
The left most box is just my wordpress website hosted on Google Cloud Platform (GCP). Visitors and users then interacts with the form to hit the HTTP endpoint served by Cloud Run in the second box, which in turn uses code from the HairyTurtle library in the third box.
Both the server and the library are stored in my GitHub repository. The CI/CD pipeline monitors git push to the master branch for bug fixes or when a Dev branch is merged, in order to trigger a Cloud Build. The build process is very straight forward:
HairyTurtle library
The library contains a few objects, interfaced by two modules, a CSV handler and the Trade Analyzer. Having a setup.py configured allows me to package the module using python3 setup.py bdist_wheel and pus the tagged package to a privately hosted Google Artifact Registry using twine upload --repository-url. Then it just sits there for when the server container is built.
HairyTurtle server
This is the Python Flask REST API server that handles client inputs, performing some basic file type checks, sanitize paths and filenames before processing it using the HairyTurtle library. The Dockerfile ensures HairyTurtle library is pulled from the Artifact Registry before building the server code into a container, which is then pushed to the Google Container Registry. From there, deploying to Cloud run is simply gcloud run deploy with correct parameters to where the container is stored.
Cloud Run vs Cloud Function
If you are still reading up until here, you might be interested in why I decided to use Cloud Run instead of Cloud Function. First and the biggest decision factor is because at the time of writing, Cloud Function does not allow me to use a privately hosted artifact in GAR (except Java packages). Second, Cloud Run provides better testability & portability for my code in case I decide to drop it to another cloud in the future. Third, it also provides better concurrency when the container is run. If there is a cold start anyway, might as well handle a few more requests at the same time without necessarily creating more instances of the function to handle requests from the same user. Lastly, the cold start for Cloud Run containers seems to be a lot shorter than the cold start for Cloud Function, which improves the responsiveness of my REST API.
The only compromise in the process is that I lose the isolation as provided by Cloud Function. However, this can be overcome by the introduction of unique ID that is generated on the client-side and communicated to the server for every session. I also make use of timestamps to ensure data cannot be leaked to the wrong client.
Things I would do differently
This exercise has taught me many things. I never really had a set time to work on side-projects like this, and it’s always the frustration near the EOFY that forces a bit more work to be done. This year was no exception. So my first take-away, if you have a hobby project, set aside some time every week to get it done without distraction. I’m sure your family and partner will understand. The discreet burst of development work created a disjointed effort and solution that could’ve been done a lot quicker without having to refresh my memory on what I did last and what to do next. A roadmap will probably help too.
Technically, I would probably have gone with Go instead of Python. It is a lot faster and creates a much smaller container image for Cloud Run.
Future improvements
- Support more broker / trading platform export files
- Support for SMSF’s 33% discount instead of 50%
- Provide friendlier UI and error messages for unexpected column parsing
- Provide portfolio tracking feature, or portfolio trend analysis from a given historical sets of trades
- Cover more types of edge cases. Some I can think of include merger/demerger and share-buyback
Reach out if you have a feature you wish to implement or think is worth adding!
As always, if there’s anything you’re unsure of, consult your accountant. None of the information here are provided as an advice.
Thank you for reading, share it if you find it helpful!
Epilogue
Since I published this post and made HairyTurtle live on my website, I have stumbled on a few Reddit threads that are asking the same question. One such post here has been archived which means I can no longer comment on, but contains a pretty dire error in the comments section.
The scenario used was:
Share A profit of $2000 held for more than 1 year
Share B profit of $1000 held for less than 1 year
Share C loss of $2000 held for 1 month.According to the resounding agreement in the comment section, going by ATO’s rule of “applying losses before applying the discount”, was to split the loss portion of $2000 into two, first offsetting Share B’s profit, and then reducing profit of Share A to $1000, and then applying 50% discount to that portion, resulting in the person having to pay tax on the $500 profit.
This is like saying: “If I have any shares held for more than 1 year with profit, I can offset THE ENTIRE portfolio’s net capital gain”, which is plain wrong.
Another commenter asked if it should’ve been Share A getting the CGT discount first, making profit portion to be taxed for Share A equals to $1000, then add Share B $1000 profit, minus $2000 Share C loss, making portion to be paid tax on = ($2000 * 50%)+$1000-$2000 = $0.
This commenter had the right idea, AND still abiding by ATO’s rule of “applying losses before applying the discount”. How? Because it’s per asset.
Check the Commonwealth Consolidated Act, it even says “Step 5: Add up any remaining capital gains that are not discount capital gains;”
Take this following tweaked scenario as an example:
Share A profit of $10 held for more than 1 year
Share B profit of $3000 held for less than 1 year
Share C loss of $2000 held for 1 month.By definition, you are only allowed the CGT discount on the $10 portion because that’s what you held for more than 1 year. If you go by the Reddit’s post answer, you would be paying tax on the portion: ($10+$3000-$2000) * 50% = $505. So .. what?! Just because you held one share more than one year that happens to be $10 profit, you get your whole lot of $1010 tax portion discounted by half? This is plain wrong.
By definition, ATO’s rule of “applying losses before applying the discount” is PER ASSET.
The tweaked scenario’s tax portion should’ve been ($10 * 50%)+$3000-$2000 = $1005.
The commenter’s answer would’ve been correct if they were all of the same Share A, bought and sold in different lots.
In conclusion, as explained above, a simple ATO rule has created all these chaos, and it’s no wonder there isn’t one calculator out there that’s doing this already.
Please do check out HairyTurtle and let me know if you found any bugs, along with the scenario you tested against! Have fun!