Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
Hi, folks,
Given a sheet of data about stock sales, I want to combine the lots that were sold on the same day for the same price for the same stock symbols. For example, say I have: A B C D E F G H I 1 Symbol Shares Date Bot PPrice Cost Date Sold SPrice Proceeds G/L 2 TSRA 100 3/1/2007 38.56 3,856.00 3/21/2007 41.05 4,104.93 248.93 3 TSRA 100 3/5/2007 37.66 3,766.00 3/21/2007 41.64 4,163.93 397.93 4 TSRA 30 3/2/2007 38.56 1,156.80 3/21/2007 41.05 1,231.48 74.68 5 TSRA 30 3/5/2007 37.66 1,129.80 4/5/2007 42.03 1,260.88 131.08 6 XRX 10 3/2/2007 16.86 168.60 4/18/2007 18.12 181.20 12.60 7 XRX 290 3/2/2007 16.86 4,889.40 4/20/2007 18.68 5,417.02 527.62 8 XRX 290 2/27/2007 17.36 5,034.40 4/18/2007 18.12 5,254.71 220.31 I will be writing lines to a new worksheet and combining the lots. So lines 2 and 4 as well as lines 6 and 8 will have their data combined. Column B on the new sheet for that sale will say, for lines 2 & 4, 130 shares. For lines 6 & 8, 300 shares. I'll average the date bought, weighted based on the dollar amount of the sale. Ditto purchase price. Cost ought to still be the sum of the combined cost lines. Date Sold doesn't change -- it's one of the bases for combining data. Ditto Sale Price. Proceeds will be a simple sum, as will gain/loss. Any ideas would be much appreciated. -- dman |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
dman,
I would use the pivot table function located in menu Data Pivottable and pivotchart. This allows you to make sum, average, count, etc. for groups of columns with same values. Stephane. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
Folks, I really could use some help here. I've spent hours
over the last few days flailing around without quite getting what I need. I can do most of the work myself once I just get a kick-start from somebody. Maybe I should just ask one question at a time. Okay, given a table like the one below, I need a formula -- I guess an array formula -- to tell me the total shares sold per day ("Date Sold") per specific selling price ("SPrice") for each symbol. Thanks for any tips! A B C D E F G H I 1 Symbol Shares Date Bot PPrice Cost Date Sold SPrice Proceeds G/L 2 TSRA 100 3/1/2007 38.56 3,856.00 3/21/2007 41.05 4,104.93 248.93 3 TSRA 100 3/5/2007 37.66 3,766.00 3/21/2007 41.64 4,163.93 397.93 4 TSRA 30 3/2/2007 38.56 1,156.80 3/21/2007 41.05 1,231.48 74.68 5 TSRA 30 3/5/2007 37.66 1,129.80 4/5/2007 42.03 1,260.88 131.08 6 XRX 10 3/2/2007 16.86 168.60 4/18/2007 18.12 181.20 12.60 7 XRX 290 3/2/2007 16.86 4,889.40 4/20/2007 18.68 5,417.02 527.62 8 XRX 290 2/27/2007 17.36 5,034.40 4/18/2007 18.12 5,254.71 220.31 -- Dallman |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
One venture ..
Assuming your source table as posted is in sheet: X, data within A2:I8 Put in J2, normal ENTER: =SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2) *B$2:B$8) Copy down to J8 Then in another sheet, you have the symbols and dates sold listed in A1:B2, eg: TSRA 3/21/2007 XRX 4/18/2007 Put in C1, array-enter (CSE): =IF(COUNTA(A1:B1)<2,"",INDEX(X!J$2:J$8,MATCH(1,(X! A$2:A$8=A1)*(X!F$2:F$8=B1),0))) Copy C1 down to return required results, viz 130 (TSRA) and 300 (XRX). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dallman Ross" <dman@localhost. wrote in message ... Folks, I really could use some help here. I've spent hours over the last few days flailing around without quite getting what I need. I can do most of the work myself once I just get a kick-start from somebody. Maybe I should just ask one question at a time. Okay, given a table like the one below, I need a formula -- I guess an array formula -- to tell me the total shares sold per day ("Date Sold") per specific selling price ("SPrice") for each symbol. Thanks for any tips! A B C D E F G H I 1 Symbol Shares Date Bot PPrice Cost Date Sold SPrice Proceeds G/L 2 TSRA 100 3/1/2007 38.56 3,856.00 3/21/2007 41.05 4,104.93 248.93 3 TSRA 100 3/5/2007 37.66 3,766.00 3/21/2007 41.64 4,163.93 397.93 4 TSRA 30 3/2/2007 38.56 1,156.80 3/21/2007 41.05 1,231.48 74.68 5 TSRA 30 3/5/2007 37.66 1,129.80 4/5/2007 42.03 1,260.88 131.08 6 XRX 10 3/2/2007 16.86 168.60 4/18/2007 18.12 181.20 12.60 7 XRX 290 3/2/2007 16.86 4,889.40 4/20/2007 18.68 5,417.02 527.62 8 XRX 290 2/27/2007 17.36 5,034.40 4/18/2007 18.12 5,254.71 220.31 -- Dallman |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
In , Max
spake thusly: Assuming your source table as posted is in sheet: X, data within A2:I8 Put in J2, normal ENTER: =SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2) *B$2:B$8) Copy down to J8 That works! Thank you very much, Max. It also helped me figure out what about my multi-hour flailing wasn't working. I had set up named ranges, but had inadvertently gotten some things wrong that were hard to pinpoint without ever having had a working starting point to test from. Then in another sheet, you have the symbols and dates sold listed in A1:B2, eg: TSRA 3/21/2007 XRX 4/18/2007 Put in C1, array-enter (CSE): =IF(COUNTA(A1:B1)<2,"",INDEX(X!J$2:J$8,MATCH(1,(X! A$2:A$8=A1)*(X!F$2:F$8=B1),0))) Copy C1 down to return required results, viz 130 (TSRA) and 300 (XRX). Okay, I managed to get this part to return values that look sort of within expectations, but are slightly different. I'm confused about what we are trying to achieve with this part of the suggestion. Can you clarify further why we're doing this second formula on the other sheet? Here are representative side-by-side results of the values I get in Column J from your initial formula and the values in Column C on the new sheet. Notice where some of the figures (shares sold) shift around and don't match up: 330 330 100 100 100 360 100 100 100 360 100 100 100 100 350 350 100 100 That's for C23-C31 on the new sheet and the corresponding J on the original sheet. I don't really follow what we're doing here, so don't know whether those divergences are "planned." :-) So far, this is a big help in any case. Thanks again! --- "Dallman Ross" <dman@localhost. wrote in message ... Folks, I really could use some help here. I've spent hours over the last few days flailing around without quite getting what I need. I can do most of the work myself once I just get a kick-start from somebody. Maybe I should just ask one question at a time. Okay, given a table like the one below, I need a formula -- I guess an array formula -- to tell me the total shares sold per day ("Date Sold") per specific selling price ("SPrice") for each symbol. Thanks for any tips! A B C D E F G H I 1 Symbol Shares Date Bot PPrice Cost Date Sold SPrice Proceeds G/L 2 TSRA 100 3/1/2007 38.56 3,856.00 3/21/2007 41.05 4,104.93 248.93 3 TSRA 100 3/5/2007 37.66 3,766.00 3/21/2007 41.64 4,163.93 397.93 4 TSRA 30 3/2/2007 38.56 1,156.80 3/21/2007 41.05 1,231.48 74.68 5 TSRA 30 3/5/2007 37.66 1,129.80 4/5/2007 42.03 1,260.88 131.08 6 XRX 10 3/2/2007 16.86 168.60 4/18/2007 18.12 181.20 12.60 7 XRX 290 3/2/2007 16.86 4,889.40 4/20/2007 18.68 5,417.02 527.62 8 XRX 290 2/27/2007 17.36 5,034.40 4/18/2007 18.12 5,254.71 220.31 -- Dallman |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
In , Dallman Ross <dman@localhost.
spake thusly: In , Max spake thusly: =SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2) *B$2:B$8) That works! Thank you very much, Max. I should add that I have succeeded at setting up named ranges for this now. E.g.: csvColDesc set to: =OFFSET('Cur Realized - imported'!$A$1,1,0,csvRows-1) csvRows is set to: =COUNTA('Cur Realized - imported'!$L:$L) (I use Col. L for the row counter because there is some extra garbage [totals, etc.] below the data in earlier columns. This comes from my broker on downloading in CSV format.) So yur formula works as follows for me now (per cell J2): =SUMPRODUCT((csvColDesc=A2)*(csvColDateSold=F2)*(c svcolSalePrice=G2)*csvColShares) Thanks again! -- dman |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
welcome, Dallman. glad you managed to extract the cores out of the response
<g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
In , Max
spake thusly: =SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2) *B$2:B$8) I have a question about this now. It seems to me in theory that this, with relative refs, might work and be more efficient: =SUMPRODUCT((A2:A$8=A2)*(F2:F$8=F2)*(G2:G$8=G2)*B2 :B$8) But I'm not sure. I came to this thinking about how to work out copying only the lines with merged quantities (in B:B) to a new worksheet. I haven't worked it through yet. I might have to go do doing this combining in VBA so I can copy the results to the new summary sheet. If you have any more thoughts about this, they would be appreciated. Dallman |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
Here's a revised set-up which I think should deliver your original post's
intents Illustrated in this sample at: http://www.flypicture.com/download/NTIyMw== Combine lots sold on same day f same price f same stock.xls In source sheet: X, In J2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G $2:G2=G2)*B$2:B2)) In K2: =IF(A2="","",IF(SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2) *(G$2:G2=G2))1,ROWS($1:1),"")) Leave K1 blank. Select J2:K2, copy down to cover the max expected extent of source data. Col K serves to flag the lines to be copied over to the summary sheet In sheet: Summary, A1:J1 carries the same col headers as in X In A2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL (X!$K:$K,ROWS($1:1)))) Copy A2 across to J2, fill down to cover the same extent as filled in X's cols J and K -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dallman Ross" <dman@localhost. wrote in message ... In , Max spake thusly: =SUMPRODUCT((A$2:A$8=A2)*(F$2:F$8=F2)*(G$2:G$8=G2) *B$2:B$8) I have a question about this now. It seems to me in theory that this, with relative refs, might work and be more efficient: =SUMPRODUCT((A2:A$8=A2)*(F2:F$8=F2)*(G2:G$8=G2)*B2 :B$8) But I'm not sure. I came to this thinking about how to work out copying only the lines with merged quantities (in B:B) to a new worksheet. I haven't worked it through yet. I might have to go do doing this combining in VBA so I can copy the results to the new summary sheet. If you have any more thoughts about this, they would be appreciated. Dallman |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
In , Max
spake thusly: Here's a revised set-up which I think should deliver your original post's intents Illustrated in this sample at: http://www.flypicture.com/download/NTIyMw== Max, that's really super. I thank you. I didn't answer right away because it was an extremely busy work day for me. But I downloaded the sample workbook with formulas and explanations and am working through it. It looks promising, though I'm still trying to understand parts of it. The part that's on the summary sheet you included is what I so far can't quite fathom completely. It seems not to be putting all the unique data in place from the other sheet, but I probably don't understand it well enough yet. Combine lots sold on same day f same price f same stock.xls Now that we've gone this far, I'm realizing more and more that I'm going to want to go with some VBA code to do this task and deposit the result as values in the new second sheet, though. If you or anyone could help with that, it would be fantastic. The task, for review purposes here, was: take a sheet full of transaction information for stock sales and copy the data to a new sheet while combining some lots. (I'll want to do other calculation on some of the fields too, but I think I can handle that later, either on my own or with more help.) Lots (which means the number of shares reported sold in the transaction on any given row) need to be combined whenever the sale date and sales price for different rows match. People who can see the earlier articles in this thread should find some sample data; or I can supply again if need be. All the best to your tremendous helpful effort up to this point, Dallman ============== In source sheet: X, In J2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G $2:G2=G2)*B$2:B2)) In K2: =IF(A2="","",IF(SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2) *(G$2:G2=G2))1,ROWS($1:1),"")) Leave K1 blank. Select J2:K2, copy down to cover the max expected extent of source data. Col K serves to flag the lines to be copied over to the summary sheet In sheet: Summary, A1:J1 carries the same col headers as in X In A2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL (X!$K:$K,ROWS($1:1)))) Copy A2 across to J2, fill down to cover the same extent as filled in X's cols J and K |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
Sorry, think there was an error in my earlier offering
In sheet: Summary, In A2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL (X!$K:$K,ROWS($1:1)))) In A2 should be: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL (X!$K:$K,ROWS($1:1))+1)) .. It seems not to be putting all the unique data in place from the other sheet, Ok, going by the comment above, I've re-read your orig posting. I had earlier assumed that you wanted only the combined shares lines to be extracted over. I see now that you want all the uniques, ie inclusive of those "single" lines in the source which doesn't require combining. Here's the revised sample which should now accomplish it: http://www.flypicture.com/download/NjE3Mg== Combine lots sold on same day f same price f same stock_v2.xls The revised construct -------------------------- In source sheet: X, In J2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G $2:G2=G2)*B$2:B2)) In K2: =IF(A2="","",IF(OR(SUMPRODUCT((A$2:A2=A2)*(F$2:F2= F2)*(G$2:G2=G2))1,SUMPRODUCT((A$2:A$20=A2)*(F$2:F $20=F2)*(G$2:G$20=G2))=1),ROWS($1:1),"")) Leave K1 blank. Select J2:K2, copy down to cover the max expected extent of source data. Col K serves to flag the all the "unique" lines to be copied over to the summary sheet. Note that you need to change the ranges within the 2nd sumproduct (A$2:A$20, F$2:F$20, etc) to suit the actual expected extent of the source data. I used row20 as the extent arbitrarily. In sheet: Summary, A1:I1 carries the same col headers as in X In A2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL (X!$K:$K,ROWS($1:1))+1)) In B2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!J:J,SMALL (X!$K:$K,ROWS($1:1))+1)) In C2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!C:C,SMALL (X!$K:$K,ROWS($1:1))+1)) In D2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!D:D,SMALL (X!$K:$K,ROWS($1:1))+1)) In E2: =IF(B2="","",B2*D2) In F2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL (X!$K:$K,ROWS($1:1))+1)) In G2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL (X!$K:$K,ROWS($1:1))+1)) In H2: =IF(B2="","",G2*B2) In I2: =IF(B2="","",H2-E2) Select A2:I2, fill down to cover the same extent as filled in X's cols J and K. As for your comment: I'm going to want to go with some VBA code to do this task and deposit the result as values in the new second sheet, though. I'm not vba conversant enough to offer you this route. But perhaps if the expected source data extent is something that is quite fixed, think you could try recording a macro when you perform the construct steps outlined, inclusive of a couple of additional steps for an entire sheet copy n paste special as values to freeze values in Summary. Alternatively, I would suggest you try a fresh posting in .programming for insights from responders versed in vba. Since this thread is quite dated and deep, I'm not sure whether there are other responders still tracking developments here <g. All the best .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dallman Ross" <dman@localhost. wrote in message ... In , Max spake thusly: Here's a revised set-up which I think should deliver your original post's intents Illustrated in this sample at: http://www.flypicture.com/download/NTIyMw== Max, that's really super. I thank you. I didn't answer right away because it was an extremely busy work day for me. But I downloaded the sample workbook with formulas and explanations and am working through it. It looks promising, though I'm still trying to understand parts of it. The part that's on the summary sheet you included is what I so far can't quite fathom completely. It seems not to be putting all the unique data in place from the other sheet, but I probably don't understand it well enough yet. Combine lots sold on same day f same price f same stock.xls Now that we've gone this far, I'm realizing more and more that I'm going to want to go with some VBA code to do this task and deposit the result as values in the new second sheet, though. If you or anyone could help with that, it would be fantastic. The task, for review purposes here, was: take a sheet full of transaction information for stock sales and copy the data to a new sheet while combining some lots. (I'll want to do other calculation on some of the fields too, but I think I can handle that later, either on my own or with more help.) Lots (which means the number of shares reported sold in the transaction on any given row) need to be combined whenever the sale date and sales price for different rows match. People who can see the earlier articles in this thread should find some sample data; or I can supply again if need be. All the best to your tremendous helpful effort up to this point, Dallman |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
In , Max
spake thusly: I've re-read your orig posting. I had earlier assumed that you wanted only the combined shares lines to be extracted over. I see now that you want all the uniques, ie inclusive of those "single" lines in the source which doesn't require combining. Correct. Sorry for not responding sooner. My business is stocks, and as you probably noticed, it was rather a hellish week. :-) Here's the revised sample which should now accomplish it: http://www.flypicture.com/download/NjE3Mg== Combine lots sold on same day f same price f same stock_v2.xls Really neat stuff, Max. Great work! Thank you very much. Regarding my VBA ideas, you're right -- I'll re-write my goals and post to the programming group for that if I still think I need to go that route. Muchas gracias! Dallman ================================================== == The revised construct -------------------------- In source sheet: X, In J2: =IF(A2="","",SUMPRODUCT((A$2:A2=A2)*(F$2:F2=F2)*(G $2:G2=G2)*B$2:B2)) In K2: =IF(A2="","",IF(OR(SUMPRODUCT((A$2:A2=A2)*(F$2:F2= F2)*(G$2:G2=G2))1,SUMPRODUCT((A$2:A$20=A2)*(F$2:F $20=F2)*(G$2:G$20=G2))=1),ROWS($1:1),"")) Leave K1 blank. Select J2:K2, copy down to cover the max expected extent of source data. Col K serves to flag the all the "unique" lines to be copied over to the summary sheet. Note that you need to change the ranges within the 2nd sumproduct (A$2:A$20, F$2:F$20, etc) to suit the actual expected extent of the source data. I used row20 as the extent arbitrarily. In sheet: Summary, A1:I1 carries the same col headers as in X In A2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!A:A,SMALL (X!$K:$K,ROWS($1:1))+1)) In B2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!J:J,SMALL (X!$K:$K,ROWS($1:1))+1)) In C2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!C:C,SMALL (X!$K:$K,ROWS($1:1))+1)) In D2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!D:D,SMALL (X!$K:$K,ROWS($1:1))+1)) In E2: =IF(B2="","",B2*D2) In F2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL (X!$K:$K,ROWS($1:1))+1)) In G2: =IF(ROWS($1:1)COUNT(X!$K:$K),"",INDEX(X!F:F,SMALL (X!$K:$K,ROWS($1:1))+1)) In H2: =IF(B2="","",G2*B2) In I2: =IF(B2="","",H2-E2) Select A2:I2, fill down to cover the same extent as filled in X's cols J and K. As for your comment: I'm going to want to go with some VBA code to do this task and deposit the result as values in the new second sheet, though. I'm not vba conversant enough to offer you this route. But perhaps if the expected source data extent is something that is quite fixed, think you could try recording a macro when you perform the construct steps outlined, inclusive of a couple of additional steps for an entire sheet copy n paste special as values to freeze values in Summary. Alternatively, I would suggest you try a fresh posting in .programming for insights from responders versed in vba. Since this thread is quite dated and deep, I'm not sure whether there are other responders still tracking developments here <g. All the best .. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining complex data
welcome, Dallman. Thanks for posting back.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Data Validation | Excel Discussion (Misc queries) | |||
complex data validation | Excel Discussion (Misc queries) | |||
Highly Complex Totals of Data | Excel Discussion (Misc queries) | |||
chart data range too complex | Charts and Charting in Excel | |||
Combining Text Strings - Complex | Excel Worksheet Functions |