Number of days stock held

[Hank]

Hello,

Does anyone have a formula for calculating the number of days a
stock was owned? Of course its real easy if you buy 100 shares on
April 1 and sell 100 shares on July 1 but what about multiple buys and
sells of different number of shares.

As an example: Buy 100 April 1, Buy 200 May 1, Sell 50 June 1,
Sell 250 July 1. For tax purposes we need to know the average number
of days the stock was held. In the calculation, I believe you have to
use the FIFO method. I was considering something like share/days but
wasn't sure how to implement it.

Any ideas would be appreciated.

Thanks,
Hank Reed

[aaron.reese]

I dont have any code but this is how I woudl lay out the data:-

Table - Stocks
... Holds stock information, ( stockID [unique primary key],company,
nominal value, market traded upon etc)

Table - Purchases
... Holds Purchase information (Purchase ID [Unique Primary], Stock ID,
Purchase Date, Purch Qty, Unit Value, Total Purchase.

Table - Sales
... Holds Sales information Sales ID [Unique Primary], Stock ID, Sale
Date, Sale Qty, Unit Value, Total Sale price

Table - SP Matches
... Holds Match ID [unique Primary] , Stock ID, Purch ID, Sales ID, Qty
As each Sale could come from many purchases and one purchase could
result in many sales, the SP Matches table would be used to work out
how many of each Purchase instance went to each sale. You can then
work out the # days for each assignment and the net gain or loss.
Weighted averages can then be applied if necessary.

You would create the SP match records by looking at the sale qty and
finding the last unmatched purchase record for the stock and generating
the SP match record with the lesser of unmatched Purchase Qty or sale
qty. If the sale qty > unmatched Purchase Qty, you would need to
create a second SP Match record for the balance against the next
purchase. Obvioulsy you would want some sanity checks so that you
could not match sales with purchases that happened after the sale
(unless you are playing futures markets!!)

You would probably also need to consider what you would do for share
splits and consolidations / rights issues / sahre conversions.

Incidentally this technique is also very useful for matching invoices
to reciepts if you are running any sort of accouting functions.

[Hank]

Aaron,

Thanks for the input. I actually download the transactions from my
stock site (unfortunately in .csv format) I convert them to an .xls and
then import them straight into my database. So all the buys, sells,
dividends, and splits are in one table, but they are clearly marked and
easy to sort through.

Your approach makes sense. Each Sell must be reconciled against a
previous Buy. I don't do short sales or futures. Using the FIFO
idea, which is what you are doing, I can calculate the actual number of
days each share is held. I'm sure you know that you are taxed much
higher on profits on stocks held less that one year. Kind of takes the
fun out of day trading, doesn't it?

The dividends only effect profit and loss, and not days held, so I
ignore them in this calculation. However, it was good that you pointed
out the situation with the stock splits ( I forgot ) because they are
just another Buy with no cost. And again we are just concerned with
days here, not dollars.

I do keep a separate table with the basic stock information, like
Symbol, Company Name, and almost anything else I can dredge up from the
stock analysis screen. I call them tiebreakers and you can pick
anything that you think will help make a decision. Analyst's
recommendations, Technical analysis, PE, Volume, et cetera.

In my day job ;-), I do reconcile Invoices but, unlike my stock shares,
they are serialized or at least numbered.

I'll keep working on it.

Thanks,
Hank Reed