Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
identify first date a record appears
Hi, I have four columns of data and would like to identify: 1-what is the first date a symbol was bought or sold 2-On what dates did the number of shares for that symbol = 0 (like if we bought 1000 and sold a total of 1000 a few days later) Does anybody have any thoughts on this? I am at a loss and would love your input! Date Symbol Action Shares 12/1/2005 BBB Sell -1200 12/1/2005 FFF Sell -1000 11/30/2005 BBB Buy 400 11/30/2005 AAA Sell -200 11/29/2005 GGG Sell -500 11/29/2005 CCC Sell -1000 11/28/2005 DDD Buy 700 11/28/2005 BBB Sell -200 11/25/2005 GGG Sell -500 11/23/2005 GGG Buy 1000 11/23/2005 FFF Buy 1000 11/23/2005 EEE Buy 1000 11/23/2005 DDD Sell -1000 11/22/2005 CCC Buy 1000 11/22/2005 BBB Buy 1000 11/22/2005 AAA Sell -1000 -- mcarrington ------------------------------------------------------------------------ mcarrington's Profile: http://www.excelforum.com/member.php...o&userid=21641 View this thread: http://www.excelforum.com/showthread...hreadid=492832 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
identify first date a record appears
Let A1:D17 house the sample you provided, with A1:D1 housing the labels
Date, Symbol, etc. In E2 enter & copy down: =SUMIF($B$2:B2,B2,$D$2:D2) F2: FFF which is a symbol of interest. G1: Buy H1: Sell I1: 0 G2: =MIN(IF($B$2:$B$17=$F2,IF($C$2:$C$17=G$1,$A$2:$A$1 7))) which you need to confirm with control+shift+enter then copy across to H2. I2: =INDEX($A$2:$A$17,MATCH(1,($B$2:$B$17=F2)*($E$2:$E $17=I$1),0)) which you also need to confirm with control+shift+enter. Hope the latter is sufficient regarding your 2nd query. mcarrington wrote: Hi, I have four columns of data and would like to identify: 1-what is the first date a symbol was bought or sold 2-On what dates did the number of shares for that symbol = 0 (like if we bought 1000 and sold a total of 1000 a few days later) Does anybody have any thoughts on this? I am at a loss and would love your input! Date Symbol Action Shares 12/1/2005 BBB Sell -1200 12/1/2005 FFF Sell -1000 11/30/2005 BBB Buy 400 11/30/2005 AAA Sell -200 11/29/2005 GGG Sell -500 11/29/2005 CCC Sell -1000 11/28/2005 DDD Buy 700 11/28/2005 BBB Sell -200 11/25/2005 GGG Sell -500 11/23/2005 GGG Buy 1000 11/23/2005 FFF Buy 1000 11/23/2005 EEE Buy 1000 11/23/2005 DDD Sell -1000 11/22/2005 CCC Buy 1000 11/22/2005 BBB Buy 1000 11/22/2005 AAA Sell -1000 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
identify first date a record appears
Thanks Aladin, the formulas seem to be on the right track. However, I am having a couple of issues. The only date that is pulled up is the first one on the list. The formula seems ot be just taking the min date on the list, instead of the first date of buy or sell. Also, does confirming by using control+shift+enter do? To my knowledge I've never used that command and it doesn't appear to do anything when I tried it. Thanks again for you response. Megan -- mcarrington ------------------------------------------------------------------------ mcarrington's Profile: http://www.excelforum.com/member.php...o&userid=21641 View this thread: http://www.excelforum.com/showthread...hreadid=492832 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
identify first date a record appears
You must press the control, shift, and enter keys at the same time. Also, lookup "array formulas" in Excel's Help for an intro. mcarrington wrote: Thanks Aladin, the formulas seem to be on the right track. However, I am having a couple of issues. The only date that is pulled up is the first one on the list. The formula seems ot be just taking the min date on the list, instead of the first date of buy or sell. Also, does confirming by using control+shift+enter do? To my knowledge I've never used that command and it doesn't appear to do anything when I tried it. Thanks again for you response. Megan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Can a date be used for conditional formatting? | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
date and time | New Users to Excel | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) |