Spreadsheet to calculate Pip P/L from Duka's Position Report

Hey Guys

Here is a spreadsheet i made up to calculate Pip P/L, Slippage, and some basic trade stats from Dukascopy's Position Report. To use it, export your position report from their website. Open the "report.csv" that is downloaded, and sort it ascendingly through Position ID. Then copy and paste this into Position Report Tab of this workbook starting in cell A3.

There is also a second sheet that calculates the slippage on pending orders. To do this you have to open up your trade log, and from the "State" drop down menu select "Filled". Copy and paste the webpage entries to a new spread sheet, starting in cell A1. Highlight column G, and press F5, and select "special", then select "blanks" and hit ok. Then right click on the highlighted cells and select "delete..." and select "entire row". Paste the remaining values into the slippage spread sheet.

Thanks, will have a look later.

First i have to install Open Office

It works, cool.

But had to paste it in B3 not A3.

There arent a Pos.Type column in the CSV File.
Starts with Pos. ID (B3)

Ok. You don't have position type because you mustn't have "merged" any positions, so you can start with Position ID, in cell B3.

Yes, do sort ascending by position id, by default it is sort by currency pair.

I always thought i will never have to work with xls files..
Avoided it successfully. Till now.

In W21 column you calculate

=SUMME(N$1:N$1048576) the commission in pounds.

What to do if i want it displayed in €. My N column is also in €.

W22 My rebate is 45 % so i think i have to write: =W21*-0.45

In W4 9 12 18 19 i have only a #NAME? displayed. Whats wrong there?

And, is it possible to have the 2 embedded images
on a separate Sheet/Tab. Would be clearer.


And, now i feel i have to learn the xls stuff and extend your
btw. nice work. Thanks.

In cell W21 i have =SUM(N:N), which results in the same thing, but i think you must have an older version i uploaded somewhere maybe... I suggest you re-download the version on the first post of this thread.

If your dukas account is in EUR, then W21 will also be in EUR, you just need to change the format of the cell (ctrl+1) from "£" to "€", the value is the same .


Yes, put whatever commission rate you get there.

again, i think you may have an older version, try re-downloading the newer version.

You should be able to just copy and paste images where you like

Thanks, done it.


But, had the newest version before and tried it again just at the moment.
Still the NAME thing..

Strange, can't think why that would be, works fine for me. Possibly because you are using open office and not Microsoft Excel, the format of the formula maybe different.

in Excel, the formulae should be:
W4 =W9/(W12*-1)
W9 =AVERAGEIF(R:R,">0",R:R)
W12 =AVERAGEIF(R:R,"<0",R:R)
W18 =AVERAGEIF(R:R,">0",T:T)
W19 =AVERAGEIF(R:R,"<0",T:T)

Next Version..