Need a Numbers template for Canadian ACB method?

I see no one has responded to your question. I am not aware of a Numbers template that will calculate the average cost of an investment. There may be something from Excel that you can import but I would be concerned about it using Excel array formulas that are not supported in Numbers.

It shouldn't be too hard to keep track of the average cost. Really you just need to keep track of the total cost and the number of shares.

1. You will have columns for # of shares transacted, purchase price (for buys), sell price (for sales), total cost, and total number of shares. Shares in will be positive. Shares out will be negative. You can also have a column for average cost, which will simply be total cost/total shares.
2. A buy adds directly to the total cost taken from the row above and to the number of shares taken from the row above.
3. A sale subtracts from the shares taken from the row above. The cost is the average cost from the row above times the number of shares sold. Subtract that from the total cost taken from the row above.
4. A stock split changes the number of total shares with no change in cost. This could be done as shares in or shares out without a purchase price or sell price. I assume a reverse split with a resulting fractional share could also include a small sale component.
5. Don't sort the table or put anything out of chronological order. Buys wouldn't be affected but splits and sales may become incorrect unless you write formulas that can handle sorting and out-of-order transactions.
6. A column with a popup of transaction type (buy,sell,split, etc) might make formulas easier or might not.

And whatever you do with reinvested dividends and the like. Not sure about that at the moment.

Those are my thoughts

My advice would be not to use a spreadsheet (Numbers or others) for this task.

The calculations in theory are not hard. But in practice they quickly get messy in a spreadsheet.

Unless you only have a small number of securities and transactions, you will be better off using software tools that are designed for portfolio management and/or tax calculations.

These typically aren't free. But they will get the job done.

SG

Have you found a version that can calculate ACB (Canadian method) the way you need, and the problem is just getting the data into it?

If so, you might want to check whether that version will import CSV data. If it does, then you might be in luck. Most banks/brokerages provide an option to download transaction data in a CSV file and you can then import that.

That's not as automated as having Quicken interface directly with the bank but it can work well. Much easier and less error-prone, I suspect, than trying to recreate the ACB calculations in a spreadsheet.

SG

Frustrating. I remember years ago having some luck with a csv to qfx converter. Not an online one, which I wouldn't trust, but I think a custom script or small app. You might want to try searching for that, including in the Quicken forums and MacScripter.

SG

Quacks wrote:

Update... can only import .csv from the mint.com website

You mean Quicken will only import .csv files generate by mint.com?

I assume you've already consulted the Quicken community? If you haven't you can find it by searching for....

import csv to quicken classic

SG