identify first date a record appears

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mcarrington - 12 Dec 2005 20:14 GMT

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

Signature

mcarrington

Reply to this Message

Aladin Akyurek - 12 Dec 2005 21:54 GMT

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$17)))

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.

> Hi, I have four columns of data and would like to identify:
> 1-what is the first date a symbol was bought or sold
[quoted text clipped - 20 lines]
> 11/22/2005 BBB Buy 1000
> 11/22/2005 AAA Sell -1000

Reply to this Message

mcarrington - 13 Dec 2005 18:32 GMT

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

Signature

mcarrington

Reply to this Message

Aladin Akyurek - 14 Dec 2005 21:08 GMT

You must press the control, shift, and enter keys at the same time.

Also, lookup "array formulas" in Excel's Help for an intro.

> 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
[quoted text clipped - 5 lines]
>
> Megan

Reply to this Message