Re: Please Help-i posted this on the work*** forum earlier and haven't had any response

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 ***. I copied your
sample data so that it occupies cells A1 to D17 in the Trading ***,
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 *** 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 *** - 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 *** 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

.