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= |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted averaging of dates
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. "Dallman Ross" <dman@localhost. wrote in message ... 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= |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted averaging of dates
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(--(colShares0),colShares,colDate)/SUMPRODUCT(--(colShares0),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 ============================ In , Fred Smith spake thusly: 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. "Dallman Ross" <dman@localhost. wrote in message ... 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= |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weighted averaging of dates
Good to hear. Glad I could help. Thanks for the feedback.
Fred. "Dallman Ross" <dman@localhost. wrote in message ... 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(--(colShares0),colShares,colDate)/SUMPRODUCT(--(colShares0),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 ============================ In , Fred Smith spake thusly: 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. "Dallman Ross" <dman@localhost. wrote in message ... 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= |
Reply |
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 |