Using data from one sheet in another, comparing values

[b4063r]

Hi All,

I have a problem I'm trying to solve and am hoping someone can help. I have two worksheets that contain data relating to trades I have taken on currency pairs. Both sets of data relate to the same trades, but they are reported in different formats and I need to try and combine both of them so that I can perform further analysis. I have attached a spreadsheet with sample data. The first sheet, called Slippage, details all trade transaction. So both those that are opening and those that are closing positions. These are listed in order of when they were placed by time. I have already performed some analysis on this sheet as I want to see what price my trades are filled at versus the price I put in the market. This is shown in column Q and is calculated by the difference between columns M and N. Which one is subtracted from the other is dependent upon whether the opening order was a buy or sell.

What I'm trying to achieve now is to have the net profit/loss for each trade show against the opening order for that trade. In order to do this we need to know which was the opening order and which was the closing order. The Profit/Loss in column P for each part of the transaction can then be summed in the same row as the opening order. Just to clarify, opening orders are identified in column H (order type) by 'Stop'. Closing orders are identified by 'Trailing Stop'. The only was to see which orders match is to use the information contained in the sheet called 'Orders'. Each line in this sheet details which orders are related. So I need to somehow be able to use this data to perform the actions on the 'Slippage' sheet.

Other than going through it line by line, I'm unsure how to do this. The actual data I want to analyse has a few thousand entries in it, so the manual method isn't really an option.

I don't mind if the Net P/L data is added as a column at the end of the 'Slippage' sheet as I've done in the example, or added to the 'Orders' sheet, or if a new sheet is created with the data, such as I've shown in the 'Output' sheet.

This might be achievable through using a VLookup, but I'm sure how to go about using it.

Thanks
b4063r

[b4063r]

Ok, so I managed to solve this problem on my own, with the help of this video: http://www.youtube.com/watch?v=981MeKFA4W8

Spreadsheet is attached for those that might be interested

[Bobsone]

Hi
Glad to see you found a solution...
I was fiddling with it and had made some progress, still haven't got it to work properly on the Slippage page.

I have included what I have done in the attachment. The Orders page cells N2:N68 have the sum of my efforts, it looks like there are 2 strikingly similar results in Orders rows 38 and 39.

Regards.