Please Help-i posted this on the worksheet forum earlier and haven't had any response

[mcarrington]

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

[PY & Associates]

keeping your data layout, we suggest:

1, use Find method by Symbol to return earliest date. Vlookup may require
you to swap date column position.

2, Sort by Symbol, do some arithmatics, then flag out all last date the
subtotals are zero. This is for all shares, not just one.

3, if you only want one share with zero balance, you do (2) first, then (1)
on the result but show last date

"mcarrington" <[email protected]>
wrote in message
news:[email protected]...
>
> 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=492877
>

[Pete]

You have arranged your dates as latest first - this will cause some
problems as you will constantly be inserting new rows for new data and
you will have to copy any formulae upwards. Do the dates have to be in
this order?

How many different symbols are you likely to have? In your example you
show 7, but is this representative, or are you likely to have hundreds
in practice?

Do you want to know every date that a symbol has zero shares (i.e.
allowing for re-buying and selling), or just the latest date that this
occurred?

Presumably your question 1 refers to two dates - date first bought and
date first sold?

Pete

[mcarrington]

Thanks for your insightful questions Pete.
1. no I can reverse the order of the data
2. I have 100's of symbols and 1000's of trades
3. Yes, I would like to account rebuying and reselling, so ideally I could pinpoint every date that has 0 shares.
4. Yes, I am trying to find the first day we bought or sold the shares and the date that we closed the position (0 shares left).

Thanks again,
Megan

[Pete]

Okay, this is not a complete solution, but it might start you on your
way. I think you will need two worksheets - one for the detailed
trading (as in your sample) and another summary sheet. I copied your
sample data so that it occupies cells A1 to D17 in the Trading sheet,
with the headings in row 1. I sorted the data so that the latest date
is at the bottom, and set up named ranges for each column - in practice
these named ranges will be much longer than the sample, so if you make
them, say A2:A65522 etc, this will enable you to add new transactions
at the bottom of your data. A 5th column is called Symbol_exist.

In the summary sheet I used 4 columns - Unique_Symbols,
Date_first_bought, Date_first_sold and Current_shares. Unique_Symbols
is an extract from the Symbol column on the Trading sheet - in the
sample there are only 7 symbols, so it was easier to type these in, but
you can obtain unique values from your symbol list by using advanced
filter. As you add transactions you will need to add new symbols to the
Unique_symbols column and extend its named range.

The following formula is copied down the Symbol_exist column:

=IF(ISNA(VLOOKUP(Symbol,Unique_Symbols,1,0)),"no","yes")

As you add a new transaction, this will tell you if the symbol exists
or not.

The following formulae can be used to give you the Date-first-bought
and Date-first-sold. These are array formulae, so commit with
CTRL-SHIFT-ENTER and then copy down for as many Unique_symbols that you
have:

[B2] =MIN(IF((Symbol=A2)*(Action="Buy"),Date,""))

[C2[ =MIN(IF((Symbol=A2)*(Action="Sell"),Date,""))

The cells should be formatted as date, and I've also set a conditional
format so if the cell value is zero the foreground colour is set to
white so you can easily see where there are no appropriate dates.

The current cumulative number of shares is given by:

[D2]
=SUM(IF((Symbol=A2)*(Action="Buy"),Shares,IF((Symbol=A2)*(Action="Sell"),-Shares,0)))

This is another array formula, and can be copied down once committed
with CSE.

I can't take this any further right now, but I would envisage that you
would use column E of the summary sheet for a "date of last transaction
resulting in zero shares". This would be filled by some VBA code which
you would run after you enter some transactions. The code would scan
the current cumulative column looking for zero values, then check the
last-transaction date column with the transactions just entered, and
record the later date in successive columns if the dates are not the
same (and then reset the last transaction date in column E). This way
you could record up to 250 dates when the share balance reached zero.

I hope this gives you some ideas on how you could progress.

Pete