Gentle people,

I have come up against a problem doing some Excel
time/cash-flow calculations and could really use some help
from the "brain trust" here.

It's a bit complicated, but let me try to break it down.
First I'll summarize the goal and the problem:

The goal is to take data on stock purchases and sales and
find the average time held per trade.

I have what seems to be a good partial solution I
concocted. The problem I'm having is reconciling unmatched
buys and sells, by which I mean share imbalances that occur
during periods of stock accumulation or divesting
(overselling, or shorting).

Here is an easy example of what I am trying to do. Suppose
I trade a stock as follows in a 3-month period. (Note that
a positive number in the Shares and $Traded columns implies
a buy. A negative number implies a sale.) [Best viewed
with a non-proportional font such as Courier.]

Symbol: Date: Shares: Price: $Traded: Commission:
MSFT 1/1/08 100 29.90 2990 -10
MSFT 1/15/08 -100 29.40 -2940 -10
MSFT 2/1/08 100 28.90 2890 -10
MSFT 2/15/08 -100 29.65 -2965 -10
MSFT 3/1/08 100 27.90 2790 -10
MSFT 3/15/08 -100 28.15 -2815 -10

Here's what I did: separately for buys and sales, I took a
weighted average of dollars traded and dates, and then
divided by dollars traded to leave me with a weighted
"average buy-date" and "average sell date." Then I
subtracted one from the other to see the average time held.
I used SUMPRODUCT to do this. I have named ranges for the
columns. We can use the header names for those in the
explanation here.

So using the example data, I have for the "average purchase
date":

[ (gross dollars from buys x dates) - (commissions x dates) /
(gross dollars from buys - commissions) ]

=(SUMPRODUCT(--(colTraded0),colTraded,colDate)- _
SUMPRODUCT(--(colTraded0),colComm,colDate))/ _
(SUMPRODUCT(--(colTraded0),colTraded)- _
SUMPRODUCT(--(colTraded0),colComm))

(The formula is, however, of course on one line without the
"_" continuation marks.)

And I have for the "average sale date" the exact same
thing, except that the now becomes a <.

The dollar-weighted "average purchase date" via the
formula, formatted as a date, is 1/30/08. And looking at
the dates of purchases in the table above, that seems
correct: I bought on the first of the month for three
months, and the price paid was not very different each
time. Since I started the table with 1/1/08, well, on
average I do seem to have bought about 30 days after the
period being considered began.

The "average sale date" comes out to 2/13/08. That also
seems intuitively correct based on the data shown in my
table.

So I have an "average days held" of 2/13/08 - 1/30/08, or
14.265 days when I work it out in Excel. Great! That's
just what I wanted to know: how long am I holding each
trade on average?

Now we finally come to my big problem.

What if I never made the final sale? Now I've bought 300
shares but only sold 200. (In case it helps, I do have a
column showing total accumulation of shares. It can also
go negative.)

Well, if I remove the final sale from my example table and
apply my formula, Excel give an "average time held" of -
0.078 days! What can I do to offset the share discrepancy?
In other words, HELP! :-)

=dman=