Caution! This Excel table performs VERY VERY MANY calculations! I will not take any responsibility if it crashes your machine! USE AT YOUR OWN RISK! (It works on my end though.)


Change Log:


Various error corrections
Various little improvements
Added Pivot table with various stats
Added Pivot table with net profit per day
Added Monte Carlo simulation (50 iterations)
Possible to add methods now (only the first two will displayed on the Overview tab)
Instruments are not limited anymore
Embedded two little macros to update the pivot tables
If you don’t trust them just disable them but then you have to unprotect the sheets containing the pivot tables to update them (I can also post a macro-free version if anyone wishes so)
Code looks like this:

 

Code

Private Sub Worksheet_Activate() Dim pt As PivotTable Me.Protect Password:="", UserInterfaceOnly:=True, AllowUsingPivotTables:=True For Each pt In Me.PivotTables pt.RefreshTable Next pt End Sub

If anything doesn't work as intended or if you want more features, just shout.

Edit: new version, corrected glitch in code.
vvhg

New version, added some functionality:

Methods can now be unlimited, two user selectable methods display in Overview tab (can be changed any time, there is a drop down list in the cells containing the heading in Overview).
Added table displaying Profit per date.
Various minor improvenents.

Have fun.

vvhg

PS: Is actually anyone interested in this template (perhaps even uses it) or is it just me??

vvhg 

New version, added some functionality: Methods can now be unlimited, two user selectable methods display in Overview tab (can be changed any time, there is a drop down list in the cells containing the heading in Overview). Added table displaying Profit per date. Various minor improvenents. Have fun. vvhg PS: Is actually anyone interested in this template (perhaps even uses it) or is it just me??

I am DEFINITELY interested as my Excel skills are nananna ... I woould really like a way that this could be incorporated into optimization of strategies so that all itinerations of an optimizations would be available on a daily basis to augment as a DIY walk-forward. Obviously that is a project in itself ... will be looking at this very closely once I get the automation of the strategy completed!

Thanks mucho!
Jon

vvhg 

PS: Is actually anyone interested in this template (perhaps even uses it) or is it just me??

Yes I am, although I have started to use the TJS spreadsheet (tradingspreadsheets.com) after I decided it was too much work for me to modify Mike's earlier version

It would have been very nice is the TJS spreadsheets had MAE/MFE provisions but they don't, unfortunately. I'm going to check out the template again in a couple of weeks time and might be able to provide some feedback for additional functionality if you wish. Recording trades is very, very important (imho) so it does pay off to put time and effort into this.

Cheers, PTG.

Hello,

How can I change the colour of the column AN? I only want to see green and red and not licht green green purple red etc... If it is green and red only it is better to see.

Best regards,
Joćo Soares

Hello jsengxx,

1. unprotect the sheet
2. select AN column
3. go to home tab
4. go to conditional formatting
5. select highlight cells rules
6. select greater than
7. rule: >0
8. custom formatting-> select green
9. go back to step 6 then select smaller than <0 red

vvhg

VVHG,

Ok. Thanks....

I have a question, How is the win loss ratio calculated?
I see in your spreadsheet that if I lief the MAE and Efficiency values unfilled the win loss calculation is not don. I think that the win loss calculation is always done with the initial stop loss, that is the amount of ticks your are willing too lose.

The MAE is a data that can be useful for stop loss calculation. If for example you have made 200 trades with an initial stop loss of 12 ticks and all your winning trades have an MAE of 6 ticks than you can tighten your stop loss to 7 ticks so now your losing trade will only lose 7 ticks instead of 12 ticks.

Best regards,
Joćo Soares

The win loss ratios are not good in this spreadsheet.
The formula is:
Win loss ratio = (Target price - Purchase price)/(Purchase price - Stop loss)

AFAIK that would be R/R!?

vvhg

vvhg 

AFAIK that would be R/R!? vvhg

What do you mean by that?