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

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

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

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