Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone up for what I see could be a challenge?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone up for what I see could be a challenge?
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone up for what I see could be a challenge?
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anyone up for what I see could be a challenge?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look Up challenge | Excel Worksheet Functions | |||
Here's a challenge... | Excel Worksheet Functions | |||
A challenge | Excel Programming | |||
Challenge | Excel Programming | |||
A Challenge To All!!!! | Excel Programming |