I trade futures and by year end I have many buys/sells and short/covers to
match up. Depending on the day I may have several buys before selling 1
contract. here is an example of the data I have in excel:

Date Buy Sell Desc Price

8/13/2007 1 SEP07 CBOT 5 DOW 132.95
8/13/2007 2 SEP07 CBOT 5 DOW 133.00
8/13/2007 1 SEP07 CBOT 5 DOW 133.04
8/13/2007 1 SEP07 CBOT 5 DOW 133.06
8/13/2007 1 SEP07 CBOT 5 DOW 133.07
8/13/2007 1 SEP07 CBOT 5 DOW 133.18
8/13/2007 1 SEP07 CBOT 5 DOW 133.28
8/13/2007 1 SEP07 CBOT 5 DOW 133.30
8/13/2007 1 SEP07 CBOT 5 DOW 133.34
8/13/2007 1 SEP07 CBOT 5 DOW 133.39
8/13/2007 1 SEP07 CBOT 5 DOW 133.40
8/13/2007 1 SEP07 CBOT 5 DOW 133.44
8/13/2007 1 SEP07 CBOT 5 DOW 133.49
8/13/2007 1 SEP07 CBOT 5 DOW 133.49
8/13/2007 1 SEP07 CBOT 5 DOW 133.50
8/13/2007 1 SEP07 CBOT 5 DOW 133.65
8/13/2007 1 SEP07 CBOT 5 DOW 133.65
8/13/2007 1 SEP07 CBOT 5 DOW 133.67
8/13/2007 1 SEP07 CBOT 5 DOW 133.70

The idea is to do a first in first out match up. To explain the first trade
I bought 1 contract then sold 2 contracts thus being short 1 contract ect.
Can someone please help me figure out how I might be able automate this
matching. It takes me so much time doing this line by line manually . The
ultimate would be for it to calculate the profit or loss for each trade. If
this question is beyond the scoop of this forum I apologize. I am at a loss
after several attempts to figure this out on my own.

It is hard to figure out your data, as posted... there are 5 column headings
and what look like 7 pieces of information per row of data. On top of that,
your example (buy and sell) is impossible to determine as those numbers as
lined up under each other in the posted example data. Can you reformulate
your example using, say, a dash to delineate columns of information (missing
information would be signaled with two dashes with nothing in-between them)?
Also, so whatever solution we come up with is relevant, how is your data
segregated? By that I mean, is your description (items you bought/sold) on
separate worksheets (one sheet per descriptive item as you posted data might
indicate) or are they all intermingled within the same worksheet (and you
just didn't show us other descriptive examples)?

--
Rick (MVP - Excel)


"Jeff-B" <(E-Mail Removed)> wrote in message
news:811DF880-99C1-40F1-96D9-(E-Mail Removed)...
>I trade futures and by year end I have many buys/sells and short/covers to
> match up. Depending on the day I may have several buys before selling 1
> contract. here is an example of the data I have in excel:
>
> Date Buy Sell Desc
> Price
>
> 8/13/2007 1 SEP07 CBOT 5 DOW 132.95
> 8/13/2007 2 SEP07 CBOT 5 DOW 133.00
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.04
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.06
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.07
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.18
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.28
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.30
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.34
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.39
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.40
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.44
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.49
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.49
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.50
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.65
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.65
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.67
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.70
>
> The idea is to do a first in first out match up. To explain the first
> trade
> I bought 1 contract then sold 2 contracts thus being short 1 contract ect.
> Can someone please help me figure out how I might be able automate this
> matching. It takes me so much time doing this line by line manually . The
> ultimate would be for it to calculate the profit or loss for each trade.
> If
> this question is beyond the scoop of this forum I apologize. I am at a
> loss
> after several attempts to figure this out on my own.

To add to Rick's comments, it would help if you showed us, by example, what
you want done. In other words, when you do this manually you do this, then
you do that, then you do the other. Write down a step-by-step procedure of
what you do. But more importantly, at each step, write down WHY you are
doing this step. Excel is a beast of logic and nothing more. If there is
no logic to each and every step you take, Excel cannot understand it. HTH
Otto
"Jeff-B" <(E-Mail Removed)> wrote in message
news:811DF880-99C1-40F1-96D9-(E-Mail Removed)...
>I trade futures and by year end I have many buys/sells and short/covers to
> match up. Depending on the day I may have several buys before selling 1
> contract. here is an example of the data I have in excel:
>
> Date Buy Sell Desc
> Price
>
> 8/13/2007 1 SEP07 CBOT 5 DOW 132.95
> 8/13/2007 2 SEP07 CBOT 5 DOW 133.00
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.04
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.06
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.07
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.18
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.28
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.30
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.34
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.39
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.40
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.44
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.49
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.49
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.50
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.65
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.65
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.67
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.70
>
> The idea is to do a first in first out match up. To explain the first
> trade
> I bought 1 contract then sold 2 contracts thus being short 1 contract ect.
> Can someone please help me figure out how I might be able automate this
> matching. It takes me so much time doing this line by line manually . The
> ultimate would be for it to calculate the profit or loss for each trade.
> If
> this question is beyond the scoop of this forum I apologize. I am at a
> loss
> after several attempts to figure this out on my own.

Why not invest a little bit on an off-the-shelf personal investment
application?


"Jeff-B" wrote:

> I trade futures and by year end I have many buys/sells and short/covers to
> match up. Depending on the day I may have several buys before selling 1
> contract. here is an example of the data I have in excel:
>
> Date Buy Sell Desc Price
>
> 8/13/2007 1 SEP07 CBOT 5 DOW 132.95
> 8/13/2007 2 SEP07 CBOT 5 DOW 133.00
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.04
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.06
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.07
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.18
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.28
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.30
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.34
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.39
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.40
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.44
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.49
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.49
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.50
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.65
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.65
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.67
> 8/13/2007 1 SEP07 CBOT 5 DOW 133.70
>
> The idea is to do a first in first out match up. To explain the first trade
> I bought 1 contract then sold 2 contracts thus being short 1 contract ect.
> Can someone please help me figure out how I might be able automate this
> matching. It takes me so much time doing this line by line manually . The
> ultimate would be for it to calculate the profit or loss for each trade. If
> this question is beyond the scoop of this forum I apologize. I am at a loss
> after several attempts to figure this out on my own.