Excel Advanced Trade Report

gringoh · Post by **gringoh** » Fri Jan 16, 2015 1:11 am

Hi,

You can find in this thread an Excel spreadsheet which generates various statements to analyze trades.

The process to generate the statements is almost automatic, it takes 3 minutes to generate a data base with the account history (thanks to an EA), and then you only have to click on button in Excel to update the data.

Screenshot:

1.JPG

You can note on the top of the report some slicers. Slicers are one-click filtering controls that narrow the portion of a data set shown in PivotTables. You can click multiple slicers to filter and analyze your trades.

Slicers available:
=============================
- Account number
- Magic number
- Comments (strategy)
- Symbol
- Various periods (year, week, day, day nb, hours, trade duration)

Informations per column:
=============================
First part:
- Total number of trades
- Trades win
- Trades loss
- Average % trades win
- Total number of buy trades
- Total number of sell trades

Second part:
- Total cash result
- Total profit
- Total loss

Third part:
- Total pips result
- Total pips profit
- Average pips profit
- Total pips loss
- Average pips loss

Fourth part:
- Total traded lots
- Total buy lots
- Total sell lots

Reports available:
=============================
- Breakdown per account
- Breakdown per trade
- Breakdown per strategy
- Breakdown per currency
- Breakdown per month
- Breakdown per week
- Breakdown per day
- Breakdown per hour

Example of a custom report:
If you use the report with the breakdown per day, you can filter trades results:
- only trades on Tuesday
- only with EUR currency
- only trades opened between 2am & 3 am
....

The spreadsheet has been developed with Microsoft Excel 2013. I don’t know if this spreadsheet is working with previous version or with Open office.

Process explained in the attached guide.

Cheers,
G

Vlokkies · Post by **Vlokkies** » Fri Jan 16, 2015 2:57 am

Thanks Gringoh, this is pure genius!

Post by **nhtqngn14** » Fri Jan 16, 2015 6:49 am

Thank you so much. it's a wonderful gift for all of us. :hi:

Post by **trendinator** » Fri Jan 16, 2015 8:03 am

looks interesting and well thought out. ill give it a go!

gringoh · Post by **gringoh** » Fri Jan 16, 2015 9:28 am

Analyzer guide updated in post 1 with informations on how to create a master data base to analyze multiple accounts.

Cheers,
G

Post by **taipan** » Fri Jan 16, 2015 1:58 pm

Thanks Gringoh to share a great analyser tools. You are the best.

gringoh · Post by **gringoh** » Fri Jan 16, 2015 3:44 pm

Hi,

I have just modified the EA to exclude “cancelled” trades from the csv file. Post 1 updated with latest version.

Those cancelled trades were polluting the reports.

Cheers,
G

erikskenne · Post by **erikskenne** » Fri Jan 16, 2015 8:37 pm

Thanks mate, you are one very special person,

Post by **macman** » Fri Jan 23, 2015 9:11 pm

Thanks very much for this.
Sadly does not work with Excel 2007 or Open Office, so it looks like you will need Excel 2013 to use this.

My Excel skills are very basic, so it may be possible to force a connection in the older Excel versions if you are good with Excel.

Scratching around to see if we have a 2013 version somewhere ....

Post by **WhoKnows** » Sat Jan 24, 2015 7:22 am

Thanks, it looks like a very useful tool. I played with your example file.

Unfortunately I can't get the .csv file to load with my Office2010.
The refresh all doesn't do anything.

You might have convinced me to upgrade to Office 2013

Will have a look at that new Office 365 system and retry in the future as I would like to use this beautiful tool.

Thamks,
Peter