Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted averaging of dates
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= |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging Values between Two Dates/Times | New Users to Excel | |||
Averaging a Value between Two Serial Dates | New Users to Excel | |||
Averaging Value between Two Serial Dates | Links and Linking in Excel | |||
Averaging a Value Between Two Serial Dates | Excel Worksheet Functions | |||
Averaging Values between Two Dates/Times | Excel Worksheet Functions |