Oanda trade statistics

Hello all,

I believe in statistics - I think if we have a look at someone's stats, it says a lot about the trader's style, performance and habits. Position traders won't have many 10 pip trades and scalpers won't have trades that last for days. It is also interesting to have an eye on what % of your trades were profitable, did you win/lose more on shorts that longs etc etc.

I think there are many many good and profitable strats and I don't want to open a discussion on trading or MM issues. Scalping the tick charts can be as profitable as trading trends on the daily, or using some stats-based correlational strategy that cares little for support & resistance or candlestick formations.

I created a tool that can be used to evaluate our trading based on Oanda transaction logs. It's an MS Access database - I will explain all the important details and encourage others to add their modifications and post it back to the thread. You can also post ideas/requests, but I cannot guarantee that I will have time to add them.

Also please put in some effort before asking very basic things like "how can I save the trades log in Oanda".

I use this tool mainly to check if my win/loss ratio does not deviate from the yearly average. Also gives me a quick overview of how many pips I earned versus pips lost, which pairs were traded most, what was my smallest/biggest/average winner/loser, average position size etc. etc.

So here is the file along with usage instructions. Please find the thread attachments list and pick the most recent file.

1. You need Access 2000 or later installed. Please if posting back any modifications, make sure the file is saved in this format. I personally don't have the latest Office and Microsoft tends to save everything in the newst format by default.

2. Save your Oanda transaction log from the web browser as "plain html". Note you can adjust the list a bit (filters, profile etc). The statement should be in English.

3. Open the MDB file. If the file has a Transactions table already, and you want to create new stats, then you will have to delete that table first.

4. Import the html file (import wizard somewhere under File menu in Access):
-Select "first row has column names".

Other options don't matter much, probably the "Balance" field name will cause a warning because it contains a space in the html file like "Balance (USD)".
Please check column names offered by Access and make sure the last one is Balance.

5. Save the data to new table called Transactions.

6. Run the numbered queries (1...13). Those will preprocess and format the data a bit. If asked about rows changed/deleted, click OK.

Now you can use the other queries (those without numbers). The most important is ClosedTrades, which is a nice list with open-close tickets, dates and prices. You can export this trades list to Excel (right-click ClosedTrades then Export). After exporting to Excel, if you share the file, you might want to delete some columns in the XLS file for privacy - eg. Balance, OpenedUnits etc.

The other queries provide stats about your trading.

There are 2 functions in the code module that might need your attention.

First is StringToDate that converts Oanda date/time to my time (that is CET, GMT+1). So feel free to change the "DateAdd" statement in the last line to match your time zone. (No, don't ask me what's your timezone).
Nothing bad happens if you can't or don't want to delve into the code module - you will have trade times in CET, and what then?

Another function that might need your attention is PipDigits. It tells which digit after the decimal is the pip value for a certain pair. As you might have guessed, I have added only those that I traded. You don't need to add every possible pair there, only those where the "pip" is not the 4th digit after the decimal. Such an example is - gold, USD/MXN or all JPY pairs.
(Yes I know Oanda displays 5 decimals for all majors eg. GBP/USD - 1.45678 but the "8", is a pipette, so the pip is the 4th digit, that is "7".)

Not sure but I think you would have to add NOK and other krones too. It's simple to check whether you're right - have a look at the Pips row of ClosedTrades - if it's how much you earned/lost on that trade, then your PipDigits function needs no modifications.

Huh, that should be enough for a start...if interested, please find the attached zip file (forum does not accept MDB files, so had to zip it)

Happy stats creation, I hope it will help you to improve your trading, find your strengths and weaknesses too.

Small updates in the file - will post some interesting stats later today.

So. How could stats be used to improve my trading.

The attached image is a brief summary of the first seven months. More than 1k trades, approximately 66 % won, 33 % lost. A winner is not necessarily a trade that has hit TP, it just means it was closed in profit (even if in 1 pip profit only). Many times I close a position if I see the power is gone. Noone has gone broke taking profits, right?
Similarly a "loser" is a trade that was closed at a worse price than it was opened.

The stats show, that the more trades on a pair, the closer the 66% - 33% win/lose ratio gets. That's good, I think if I had 10 trades in total, it would be funny to say "I won half of the trades" etc. But with dozens of trades, the worth of stats increases.

I have created separate stats for every month and basically the win-lose ratio looks stable - it hopefully means I have found some way that works for me and I'm not changing strategies week after week.

Something that actually worries me and requires very much work in the future is the size of the average loser. I have twice as much winners as losers but the average loser is so close to the average winner. I clearly need to cut losers far more quickly. I will need to revise my trade ideas and define more strict exit points. (At the moment I use support/resistance levels). Maybe I need smaller stops. Or need to be more careful when opening positions.
Or all of the above. We'll see.

Oh, I know I netted 16k pips this year - I try to capture the bigger moves - but did I mention, at the moment I use 100 pips = 1% capital position sizes, so no, I'm not a millionaire (yet).

To sum up the stats:
1. Good win/lose ratio, which is almost the same month after month.
2. Much work required in cutting losers earlier.
3. Think about increasing position sizes - for example 75 or 50 pips = 1% capital, in parallel with #2.

Hmm, will think about it.

"No matter what I do, the key is to do it consistently." - Chicky

Stats help a lot, you can see if you're consistent.

Monthly update of Oanda stats converter - added trade duration (seconds) - just a DateDiff.
Minor fix in one of the queries.

Hello spekitox, Thanks for your sharing! All works fine.

But I have a problem when I run the queries Losspips and ProfitPips.
Everytime I doubleclick on them Access crashes. Also when I right click on Losspips, for example, and choose the second option in the context menu, access crashes, too. I don't know what's the name of this second option in English, I think it is design view. It also crashes when I choose other options in the context menu when right clicking Losspips or Profitpips. Do you have any idea?

Cheers

Hello,

glad to see someone found it useful.

I made some small changes in ProfitPips and LossPips, instead of the Transactions table, it uses the ClosedTrades view now. Dunno if it's gonna fix the problem tho..

There are some changes in this version, ClosedTrades view contains a couple of new columns:

[Opened%OfBalance] (note you must put in square brackets if you use it in queries, because of the % sign)

An example will make it clear. If you have 10000 USD on your account, and open 1 standard lot (100.000 units), this will be 1000 %, that is, you opened a position that is 10x bigger than your account. (I personally use positions around 100%, that is enough if you go for many pips with small positions)

[Closed%OfPosition] if you use partial closes, this will show how much of the position was closed with that ticket. Eg. you open a 1000 USD trade, close 750 units somewhere, then this will show 75 (75 % was closed)

BalanceAtOpen, BalanceAtClose - the Balance value when you opened/closed the position.

PL, [PL%] - profit of this trade in account base currency, and the same expressed as percent.

Note: because of PL calculation, Balance column is now used so must be imported (was ignored in previous versions), most likely needs your attention when importing - I updated post #1, please check.

Hello, thanks for your input! Now I get a runtime error on query 5 "Fix Date Data". Tried it multiple times, but with same result. Access 2000

If you can export a simple small list (one day's history), import it to Access, overwrite private infos if you like, and send me the MDB file without running the query. I will check what causes the error. Maybe some language setting, or a different date format? Also you could make a alt-prtscreen screenshot of the error dialog box.

By your post I've found the cause of the error myself. I had to change the Windows Region to English (USA) and the error disappeared.

But LossPips and ProfitPips are still causing Access to crash. It also crashes when choosing the options in the context menu of each one. Hm, strange.

Hm, thanks for the feedback. Different date formats might have caused the error.

I don't understand this Access crash, but you can sum up your profit and loss after ClosedTrades is exported to Excel. With simple formulas I think.

Hi,

I am using Access 2007 (Hu) version. When I open the .mdb file, there is a warning. I have clicked on Settings button and allowed to run the unsafe code ( someVBA macrostuf) but I can't see any tables!

Where I have made a mistake?

First import your saved Oanda history HTML as described in the 1st post.
There are no tables in the MDB.

Thanks,

I have imported, I have renamed the table... Now I have "type error in statement", but I will solve this...

Did you run the 6 queries after the import?

erhm, did you read the first post?

Fixed hopefully all problems related to regional settings. The problem was, depending on Windows regional settings, dates and numbers look very differently. For example the decimal separator can be a . or a , etc etc. Had to add a couple of preprocessing queries/updates... more than a dozen of them now.. still no obvious way to execute them at once... Will update post #1 too.

Hi,
I have made my "trade analyzer"
Which is basically a CSV parser, and display the data as Tree and with a button can calculate some data: transactions and view in a table. The table has a regular expression (text) filter and a date filter.

Which could be interesting: you can merge multiple Oanda accounts data. (I am playing with 3 demo)

It requires the transaction to be exported in cvs file format and select all fields as input file. To be able to run you need java 1.6.

If the parser thows an error: that means that file contains a type of transaction which I do not have: please tell me the name to implement

Load 3.csv with import.
If you doubleclick on a transaction in a tree you will see some info with parsed data.
Click on the transform button to see more user friendly

That's great. Java should not be a problem for Oanda users I think, as the platform itself runs in java too.

Anyways stability of the Access version was improved a lot, it should work with different regional, language settings etc.

I've fixed some problems - now works with different regional settings, time and currency formats etc. It works fine and I use it now and then for stats generation. But, generally I use MyFxBook and recommend using it. Good site, many useful tools etc etc.

Laters!

Thanks Spekitox, but where can I download your fix? It's still the older one in post #1. :-)