I want to create a document in QV to be able to analyse the trades that Investor has made.
What I have is the following:
|46544268||Pair 1||Buy||2.52E-05||362.0779||0.00911||1.82E-05||27/01/2018 3:26:00 PM|
|46419409||Pair 1||Sell||0.00051||2||0.00102||2.04E-06||28/01/2018 9:22:00 AM|
|46378308||Pair 2||Buy||2E-08||398115.8||0.007962||1.59E-05||29/01/2018 11:44:00 PM|
|46242451||Pair 2||Sell||7E-08||9050.399||0.000634||1.27E-06||30/01/2018 10:50:00 AM|
|46238564||Pair 1||Sell||6.93E-06||80||0.000554||1.11E-06||30/01/2018 12:47:00 PM|
What I need is to use this data to create report for each month for each pair:
What we have open and what we have closed (only buy or buy and sell).
The problem is that the buy and sell do not match the same amount, so there may be several buy records for one sell and one buy for multiple sell orders...
Also the time is different, so we have one day only buy next only sell, and some days buy and sell.
I am not sure if I have to aggregate the data in the load script, or afterwards in the sheets there is some SET analysis that can help me get what I need.
Also I will need to show how long each position was open before closing.