Conditional copying, profit and loss trading spreadsheet

[Tom N]

My question is how to use vba to transform a csv file of trades into a form where I can use Excel to
analyze profit and loss and to evaluate alternative trading strategies.

My trading platform generates a record of transactions that looks similar to this:

a	b	c	d
Id	Side	Price	Date/Time
1	Buy to Open	2,165.25	8/1/16 9:00
2	Buy to Open	2,167.00	8/1/16 9:10
3	Buy to Open	2,168.75	8/1/16 9:20
4	Buy to Open	2,168.25	8/1/16 9:30
5	Buy to Open	2,168.25	8/1/16 9:45
6	Buy to Open	2,171.00	8/1/16 10:10
7	Sell to Close	2,171.75	8/1/16 10:25
8	Sell to Open	2,171.75	8/1/16 10:25
9	Sell to Open	2,167.25	8/1/16 10:55
10	Buy to Close	2,161.25	8/1/16 11:30

<tbody>
</tbody>

The trade shows taking six long positions, then selling on the 7th trade. Then the account goes short twice and closes out on the tenth trade.
In this example, trades 1 to 6 are offset by trade 7, while trades 8 and 9 are offset by trade 10. Results in the real world can get even more complicated -- sometimes a trade has multiple closes as well as entries.



I have figured out how to use vba to move the values of the rows with closing trades to new columns.

Table2

Id	Side	Price	Date/Time	ClosingSide	Close_Price	ProfitOrLoss
1	Buy to Open	2,165.25	8/1/16 9:00		0
2	Buy to Open	2,167.00	8/1/16 9:10		0
3	Buy to Open	2,168.75	8/1/16 9:20		0
4	Buy to Open	2,168.25	8/1/16 9:30		0
5	Buy to Open	2,168.25	8/1/16 9:45		0
6	Buy to Open	2,171.00	8/1/16 10:10		0
7				Sell to Close	2171.75
8	Sell to Open	2,171.75	8/1/16 10:25		0
9	Sell to Open	2,167.25	8/1/16 10:55		0
10				Buy to Close	2161.25

<tbody>
</tbody>

The next step in this line of thinking would be to copy the values of the closing trades into the rows of the corresponding open positions. That would look like this:

Table3

Id	Sell to Close)	Price	Date/Time	ClosingSide	Close_Price	ProfitOrLoss
1	Buy to Open	2,165.25	8/1/16 9:00	Sell to Close	2171.75
2	Buy to Open	2,167.00	8/1/16 9:10	Sell to Close	2171.75
3	Buy to Open	2,168.75	8/1/16 9:20	Sell to Close	2171.75
4	Buy to Open	2,168.25	8/1/16 9:30	Sell to Close	2171.75
5	Buy to Open	2,168.25	8/1/16 9:45	Sell to Close	2171.75
6	Buy to Open	2,171.00	8/1/16 10:10	Sell to Close	2171.75
7	Sell to Close	2,171.75	8/1/16 10:25	Sell to Close	2171.75
8	Sell to Open	2,171.75	8/1/16 10:25	Buy to Close	2161.25
9	Sell to Open	2,167.25	8/1/16 10:55	Buy to Close	2161.25
10	Buy to Close	2,161.25	8/1/16 11:30	Buy to Close	2161.25

<tbody>
</tbody>

Then, it would be a simple matter for me to write vba that would figure profit and loss on each leg of the trade since the values used are in the same row.
Where I am stuck is how to get from "Table2" to "Table3". I have tried different sorts of loops but the complexity of the conditions (and their variety) is beyond my beginner's skill. Manually copying the values is not feasible because of the large number of trades analyzed (plus the chance of operator error.) My apologies for writing such a long post.
I have given this the old college try, but I think I need help now. Thanks in advance.

 

[Rijnsent]

Hi Tom,

is there a particular reason you want to use VBA? I gave formulas a try and that works quite good. With your data (incl headers) in A1:D11 this is the formula in E2:

=IFERROR(OFFSET($C2,MATCH(IF(B2="Buy to Open","Sell to Close",IF(B2="Sell to Open","Buy to Close",""));$B3:$B$12,0),0),0)

Hope that helps,

Koen

 

[Tom N]

Hi Koen,
Thanks for your response. It appears to have two parts ... the part with your signature and the part that seems to be a standard message. I will try to address both parts since I can appreciate that it might appear to you that I have not made an effort to solve this issue.
I mentioned vba because I have tried similar operations with both functions and with procedures, but found the procedure ran much faster than the functions, but I am willing to try anything that works.
I tried the formula you provided in cell E2. Excel said that it contained an error and suggested a fix -- it will not allow the semi-colon after the double close parenthesis. Excel will accept either a comma or colon, but both options return values of zero. The match formula is new to me, so I am reading up on it to help troubleshoot it.
Regarding my efforts to solve this problem, I began with a do loop and copy and offset the values. My thought was that I would build a procedure in a couple parts. A do-until loop would range through the open trades and stop at the close trade, then use the offset copy/paste to fill in the rows with the open trades. My idea was to nest that within another loop that went through the range of several thousand rows. I got this to work but it would fill in only the last open position. The initial open positions had to be filled in manually.
My next effort was to build a loop within a loop within a loop. The idea is that the same offset/looping process could go through all the opening trades with the closing trade, fill them in, and stop. That would be within another loop which would range through the next group of opening trades until it came to their closing order, fill them in, and stop. That would be within another loop that would stop when it got to the end of rows. This ran into all sorts of problems.... not filling in all the rows, not stopping in the right places and overwriting other rows, starting and stopping consistently on appropriate places so the loops would work together. So far what I have are segments that take more effort to operate than manually copying the values. I also read a lot on the Excel forums that discourages the use of ActiveCell.Activate etc.
My thought now is that a better approach would be to assign index values to trades to use as a reference to match the closes to the opens. For example, Excel could assign a counter of 1 to all opening trades until there is a closing trade, which would also be assigned a value of 1. The next series of opening trades would be assigned a value of 2, then a value of 2 assigned to its closing trade. In my example, trades 1 through 6 would be assigned a "1", trade 7 would be assigned as close side of "1". Then trades 8 and 9 would be assigned an index of "2" then trade 10 would be assigned to the close side of index "2". Then Excel could fill in the closing values for all open trades with the corresponding index number. I am not sure if this is a workable approach. The match formula you proposed may be doing something like that, but this is all very new to me, so it is taking some time to absorb.

 

[Rijnsent]

Hi Tom,

I notice two things about my answer: I have a European decimal setting (hence ; as separator in functions) and tried manually to replace all ; with , so you could copy-paste the formula... Apparently I failed and missed one

The correct formula should be:
=IFERROR(OFFSET($C2,MATCH(IF(B2="Buy to Open","Sell to Close",IF(B2="Sell to Open","Buy to Close","")),$B3:$B$12,0),0),0)

Basically what it should do:
If your current line is "Buy to open", search for "Sell to Close", but search only below the current row ( $B3:$B$12 -> if you drag that down, only the 3 will become 4, 5 etc, the B12 will remain as it is).
So if you take only the MATCH part of the formula: =MATCH(IF(B2="Buy to Open","Sell to Close",IF(B2="Sell to Open","Buy to Close","")),$B3:$B$12,0)
-> this should give back for every row how many rows below that row the matching closing trade is.
See this file for a sample: https://www.dropbox.com/sh/l7ywfwzfk5j20sr/AACXwUGywU-4J-IWwdZY58Ppa?dl=0 (file called OpenClosePositions.xlsx , you can check out the other files too, they are for other questions I answered on this forum)

In the Excel world, VLOOKUP and MATCH are two formulas you can't do without, so do give them a go!

And finally: my signature is definitely not aimed at you as your post is quite elaborate and I already had the feeling you gave it a good try. It's just that there are also a bunch of posters here that pose questions like "please write this code for me", those people should just hire somebody IMHO .

Hope that helps a bit,

Koen

 

[Tom N]

Koen, Thanks for the follow-up. It is going to take me some time to absorb match/vlookup before I can respond intelligently, so I wanted to note that I appreciate the resources to help understand it. TN

 

[Rijnsent]

Hi Tom,
try these tutorials, I find them pretty clear:
Lookup & Reference Functions in Excel - EASY Excel Tutorial or Excel Vlookup Tutorial
Cheers,
Koen

 

[Tom N]

Koen, The formulas worked! It helped to add the helper columns to help see how. I didn't realize that formulas could do that sort of thing. I will be checking out the videos. Thanks again!