Best stock portfolio tracker excel
Since I mostly make buy transactions and rarely do a sell transaction these days, my set up is rather simple. I simply input the date, type of transaction buy, sell or ROCno. Each stock goes into a separate sheet and US stocks have an additional column for the exchange rate. When you make a buy transaction, you should add the total value of the current transaction to the previous ACB.
If you buy another shares of XYZ Corp. When you make a sell transaction, your ACB decreases by the number of shares sold times the average ACB per share. For instance, if you had instead sold shares of XYZ Corp. Return of Capital simply reduces the ACB by the number of shares times ROC per share. For instance, if XYZ Corp. DRIP Primer also has a handy explanation of ACBs for those who reinvest their dividends.
To make Kevin happy, I cleaned up my Excel spreadsheet and added some code to automatically handle buy, sell and Return Of Capital transactions.
The spreadsheet should calculate ACB, Share Balance and Capital Gains columns. Just copy and paste the previous row to create a new transaction. You can download the spreadsheet here. There is no limit to how complicated these stock transaction spreadsheets can become. For example, I used to have to track stock options and deferred stock option gains. The critical thing is to learn enough to be able to create your own spreadsheet.
I agree that this is just a starting point. It can become a lot fancier depending on how fancy one wants to make it. I agree that automation is best, but it is hard to anticipate all possible events. There are stock splits, reverse splits, share distributions, returns of capital, spinoff of shares of other stocks such as when BCE gave Nortel shares to its shareholdersand other even more exciting possible events.
The latest update to stock option gain deferral rules forced me to add a new column to my spreadsheet. So, while I do my best to automate, I find it necessary to update my spreadsheet periodically.
When you sell, CRA requires you to claim capital gains or losses based on the ACB. There is no leeway here.
Our first Friday wrap up! Flipping Houses, Electric Cars, Market Roll Over and More Million Dollar Journey.
What Are The Easiest Ways to Track My Investments?
Wander Reading 27 Canadian Dream: I was wondering if this tool could be enhanced to support journaling from an exchange to another and still keep tracking the cost back to the original currency the securities were purchased. Journaling complicates the tracking of the ACB and also the performance of a holding.
Portfolio Slicer
Also, it is my can i buy otc stocks on sharebuilder that if a security is interlisted, then driving currency is the one the headoffice is based on, e.
Thanks for the spreadsheet! I looked at the US Stock tab, it seems it makes an assumption that the Comission is charged in CAD. I suppose I should adjust the calculation in my case?
Starting from line 3 the formula does account for Comission charged in USD. Thanks for pointing out the error. How would you best show a stock split, with this format of your spreadsheet, and thanks for this workmanship, it has sure helped me out good job.
Ericj is right, if you are investing with ETFs, you need to include the re-invested distributions in the calculation of ACB.
The spreadsheet calculates Capital Gains only when the stock is sold, however with ETFs or Mutual Funds these Capital Gains occur often within the fund as the fund manager buys and sells stocks. When this occurs, the taxes have to be paid in the year the gains occur but they also result in an increase in the ACB by that amount, unless there was also some Return of Capital in which daily trading system forex course the amount of ROC decreases the ACB.
Even interest gained within the fund say for money the fund manager has sitting while he waits to purchase stocks must be taxed in the year they occur but will result in an increase in the ACB as long as it is not.
How can I enter 0. I trade actively more than a thousand transactions per year. Rather than track the ACB on each position often held a week or two, not longerdma trading strategies I not measure best stock portfolio tracker excel gain on the best stock portfolio tracker excel portfolio during the year, and treat the gain as a capital gain as if the portfolio were one stock?
It is clear how to calculate the ACB on the portfolio. Am I right so far? No change to the ACB.
This is all return of capital; none of this money is taxable, right? Also, you pay tax on gains only when you realize the how much money does a franchise make from subway by selling your positions.
You have shares of RST left. After many years of buying mutual funds on a ongoing auto-purchase basis, I redeemed part of them last year. I started a TRP drip in my name. After a year I also started a drip joined account with my husband using one share from the drip in my name.
Now I have 2 TRP drips one in my name and one with my husband and I am wondering how to calculate ACB.
Stock Portfolio Tracking Spreadsheet - Google Sheets
Your help is much appreciated. My question is, if you have bought and sold a stock multiple times in the year and have kept track of the ACB using the spreadsheet, do you aggregate all sales for that stock into one disposition and ACB when you are filling out your taxes using software such as SimpleTax or do you need to do a separate disposition for each sale of that stock individually? Your email address will not be published.
This post has been updated as of Jan. Home Bookshelf In the News Archives Contact Us Consulting Forum. This article has 28 comments Michael James August 25, at Canadian Capitalist August 25, at Michael James August 25, at Phil S August 25, at August 25, at 4: Canadian Capitalist August 26, at TL November 16, at 5: G February 26, at 8: Canadian Capitalist February 28, at Solara March 10, at 9: Erick May 8, at 5: Jaz September 7, at 7: TH April 27, at Willy April 28, at 6: Willy April 28, at 7: I found the answer.
I edit the cell to increase the decimal places. Toby January 25, at 5: Canadian Capitalist January 26, at 1: PaddyAnne February 10, at Alice August 27, at Kal February 15, at 2: Hello, Thanks for all the great information and comments.
Alex February 18, at Now how would you calculate this scenario in this sequence? Rick March 4, at 2: Thanks you so very much!
This little spreadsheet app was just the ticket for my needs!
Leave a comment Cancel Your email address will not be published. Recommended Posts High Interest Savings Accounts at Discount Brokers January 27, — comments. A Foolproof Method to Convert Canadian Dollars into US Dollars May 25, — comments. July 29, — comments. The high cost of peace of mind November 3, — comments. Beware of tax shelter donation arrangements August 19, — comments.