How to track mutual fund shares?

[curiousgeorge408]

This problem is giving me a headache! I am struggling with
how best to set up a workbook to track various transactions
in a mutual fund.

Does anyone have a good design for that?

If so, feel free to mail it "curiousgeorge408" at "hotmail.com".

I don't like any of the the MS templates that I have looked at.

Of course, I need to track buys, including reinvested dividends,
and sells, with related basis. I also need to track splits and
"journal" transfers to other accounts in the same brokerage. I
have not yet decided whether to use actual basis or single
category average basis for shares sold. So I need to design
the workbook with that flexibility in mind.

The problem I wrestle with is: how to organize the data and
computations so that I can track how many shares remain
in each purchase lot and with what basis after several sells
over time?

Any constructive thoughts would be appreciated. My thoughts
are going in circles.

PS: I am an occassional seller, not a day trader. I tend to
sell once or twice a month.

 

[Fred Smith]

Buy Quicken. It will do everything you want. You can get it for less than
$50 on eBay.

Regards,
Fred.

 

[curiousgeorge408]

Buy Quicken. It will do everything you want.

I anticipated that response. I, too, thought about Quicken; and I
have not ruled it out. At the very least, I might look at how Quicken
organizes the information to see if its adaptable to Excel. In
general, I am not impressed with Quicken; but I'm malleable.

At any rate, this is m.p.EXCEL.misc. So I'm interested in an Excel
answer. I am sure that many people use Excel to track their
investment trades. I would like to hear from them.

I should note that my question is not so much an Excel question -- I'm
sure I can do all the necessary computation.

I simply cannot get my head around the design problem: how to lay out
the information so that it is (a) easy to maintain, and (b) easy to
match sells and buys primarily for the purpose of distinguish short-
term and long-term gains/losses (US).

I cannot imagine how I would do this manually, much less in Excel.

 

[curiousgeorge408]

I simply cannot get my head around the design problem

Never mind! I think I've got a workable solution.