Weighted averaging of dates

[Dallman Ross]

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(--(colTraded>0),colTraded,colDate)- _
SUMPRODUCT(--(colTraded>0),colComm,colDate))/ _
(SUMPRODUCT(--(colTraded>0),colTraded)- _
SUMPRODUCT(--(colTraded>0),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=

 

[Fred Smith]

Your problem is similar to calculating return on a typical investment. If
you've invested $10,000 over various periods of time, and it's currently
worth $15,000, what's your ROI? While XIRR will do the calculations, they
can be done only if you assume the investment is sold today. In other words,
the final entry is today's date and the value of the investment.

Your problem is the same. Even if you haven't sold all of the shares, the
only way to do the calculation is to assume you did. So your final entry has
to be an assumed sell for the remaining balance (or an assumed buy to cover
a short sale).

Hope his helps,
Fred.

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(--(colTraded>0),colTraded,colDate)- _
SUMPRODUCT(--(colTraded>0),colComm,colDate))/ _
(SUMPRODUCT(--(colTraded>0),colTraded)- _
SUMPRODUCT(--(colTraded>0),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=

 

[Dallman Ross]

Fred --

Brilliant thinking! That was a very helpful answer. Thank you.

At first I had my doubts. And I was thinking of problems such
as: what if I suddenly increased my investment in the particular
stock massively? It would skew the results. But that sort of
thing will always skew results in one way or another. One simply
needs to be aware of what is going on and not apply rules
rotely. And, bottom line, it works well!

It took me a few minutes to figure out how to apply your suggestion
in my formulas. While I was mulling that over, I had a realization:
my solution was not correct as presented! I have been averaging
dates as weighted by dollars invested. But that isn't right.

Taking my example table of six Microsoft transactions -- 3 buys
and 3 sales -- and thinking about it, it dawned on me that
the price should be irrelevant to the time held. By way of crazy
example, what if the first buy of MSFT had been for $299.00 a share
instead of $29.90 a share? If I sold for the previously stated
$29.40 a share 15 days later, well, that would of course be a
bank-breaking loss. But the loss ought not affect the length of
time held, which remains 15 days.

For grins I plugged in that very high share-price for the first buy
into my table with my existing formulas and saw the average time
held shift dramatically. It moved from 14.26 days to 36.68 days.

Okay, so I came to the conclusion that all I need to weight the
date with is number of shares transacted. That makes my formulas
quite a bit simpler.

The new formula for (weighted) average date bought is:

=SUMPRODUCT(--(colShares>0),colShares,colDate)/SUMPRODUCT(--(colShares>0),colShares)

The formula for average date sold is now as follows, applying your
suggestion:

=(SUMPRODUCT(--(colShares<0),colShares,colDate)-ABS(SUM(colShares))*TODAY())/(SUMPRODUCT(--(colShares<0),colShares)-ABS(SUM(colShares)))

The ABS function deals with long or short open positions.
Subtracting the first from the second formula gives me an average
days held for the set of transactions, weighted by number of shares
bought or sold. Excellent!

Thanks again,
Dallman

============================

Your problem is similar to calculating return on a typical
investment. If you've invested $10,000 over various periods of
time, and it's currently worth $15,000, what's your ROI? While
XIRR will do the calculations, they can be done only if you
assume the investment is sold today. In other words, the final
entry is today's date and the value of the investment.

Your problem is the same. Even if you haven't sold all of the
shares, the only way to do the calculation is to assume you
did. So your final entry has to be an assumed sell for the
remaining balance (or an assumed buy to cover a short sale).

Hope his helps,
Fred.

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(--(colTraded>0),colTraded,colDate)- _
SUMPRODUCT(--(colTraded>0),colComm,colDate))/ _
(SUMPRODUCT(--(colTraded>0),colTraded)- _
SUMPRODUCT(--(colTraded>0),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=

 

[Fred Smith]

Good to hear. Glad I could help. Thanks for the feedback.

Fred.

Fred --

Brilliant thinking! That was a very helpful answer. Thank you.

At first I had my doubts. And I was thinking of problems such
as: what if I suddenly increased my investment in the particular
stock massively? It would skew the results. But that sort of
thing will always skew results in one way or another. One simply
needs to be aware of what is going on and not apply rules
rotely. And, bottom line, it works well!

It took me a few minutes to figure out how to apply your suggestion
in my formulas. While I was mulling that over, I had a realization:
my solution was not correct as presented! I have been averaging
dates as weighted by dollars invested. But that isn't right.

Taking my example table of six Microsoft transactions -- 3 buys
and 3 sales -- and thinking about it, it dawned on me that
the price should be irrelevant to the time held. By way of crazy
example, what if the first buy of MSFT had been for $299.00 a share
instead of $29.90 a share? If I sold for the previously stated
$29.40 a share 15 days later, well, that would of course be a
bank-breaking loss. But the loss ought not affect the length of
time held, which remains 15 days.

For grins I plugged in that very high share-price for the first buy
into my table with my existing formulas and saw the average time
held shift dramatically. It moved from 14.26 days to 36.68 days.

Okay, so I came to the conclusion that all I need to weight the
date with is number of shares transacted. That makes my formulas
quite a bit simpler.

The new formula for (weighted) average date bought is:

=SUMPRODUCT(--(colShares>0),colShares,colDate)/SUMPRODUCT(--(colShares>0),colShares)

The formula for average date sold is now as follows, applying your
suggestion:

=(SUMPRODUCT(--(colShares<0),colShares,colDate)-ABS(SUM(colShares))*TODAY())/(SUMPRODUCT(--(colShares<0),colShares)-ABS(SUM(colShares)))

The ABS function deals with long or short open positions.
Subtracting the first from the second formula gives me an average
days held for the set of transactions, weighted by number of shares
bought or sold. Excellent!

Thanks again,
Dallman

============================

Your problem is similar to calculating return on a typical
investment. If you've invested $10,000 over various periods of
time, and it's currently worth $15,000, what's your ROI? While
XIRR will do the calculations, they can be done only if you
assume the investment is sold today. In other words, the final
entry is today's date and the value of the investment.

Your problem is the same. Even if you haven't sold all of the
shares, the only way to do the calculation is to assume you
did. So your final entry has to be an assumed sell for the
remaining balance (or an assumed buy to cover a short sale).

Hope his helps,
Fred.

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(--(colTraded>0),colTraded,colDate)- _
SUMPRODUCT(--(colTraded>0),colComm,colDate))/ _
(SUMPRODUCT(--(colTraded>0),colTraded)- _
SUMPRODUCT(--(colTraded>0),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=