Diversified Trend Trading – Excel Projects

Wow Copernicus. Kudos on the thread. Look forward to participating

Cheers V. I am not so sure about your verdict but hopefully with the contributions we get in this thread, it will blossom into something of real value for all participants. :-)

Thanks S. Looking forward to your participation mate......as I know you play in the same trend trading world I do :-)

Guys

I have just replayed the process as though I am a participant in this thread and have downloaded the files after creating my directory structure from my desktop.

It all works Ok so I am confident I haven't left any of the details out.

Please let us know if you are successful or not in getting the Basic Module fired up and running.

Unfortunately my son's footy game today was a bit rough and ended up with him nursing a dislocated shoulder so I am on call at the hospital emergency centre with taxi duties this afternoon.

Will be back on line this evening to answer any queries or problems you may be facing.

C

It's running but it has a lot of #DIV/0! and #VALUE! in the cells of the Data Dump worksheet.

Sorry to hear about your son's misfortune. Wishes for a speed recovery.

Hi B

Son is fine now but thanks for the well wishes. Only soft tissue damage....

It's probably best to delete your files and start the process again.

Try this.

Create a new directory structure from your desktop before placing any files in it. You should have a similar file structure to that displayed below.

For system files:
C:\Users\richard\Desktop\Excel Projects\Backtests\BasicModule\System Files

For datahistory files:
C:\Users\richard\Desktop\Excel Projects\Backtests\Datahistory

Once you have created the directory structure, then download the datahistory files and unpak the winrar file and copy the 5 data files to the Datahistory directory.

It should look like this when this has been done.



Then download the PC, TM and PM winrar files and extract each excel file to the System Files directory.

It should look like this when complete.



Next step - Open the PM file and then select "Alt-F8" to display the macros. Select "CollectData" so it is highlighted and then select "Edit".

Ensure the directory structure (highlighted section) points the portfolio file to the same directory where your system files reside.

It should look like this.



Save and then close the PM file.

Now open the PC file.

Go to the inputs worksheet and ensure the directory locations for both the Portfolio and the Data folder point to the correct file location reference points.




If you make changes then save the changes.

Ok...now it's time to create the portfolio.

Select the button "Create Portfolio". The program will then crunch away for about 5 mins creating your portfolio files. When excel stops calculating and processing then close the PC file and save any changes if asked.

If you then look in your system folder, you should find all the 5 instrument files sitting there. It should look like this.



We are almost there.

Now open up the PM file. It should bring you to the "Trade Summary" worksheet. Ensure you click the button "Update Data" and the entire portfolio will then be updated. It will take about 5 mins to complete.

At the end you should be presented with the "Trade Summary" screen and there should be no #DIV/0! and #VALUE! errors displayed.

Let me know how it goes.

Cheers

C

Thanks C. Second attempt has everything working now.

Glad to hear that your son does not have anything major wrong. I know it is hard to keep your mind on other things when a loved one has a major health issue.

That's great to hear B and thanks for the sentiment mate.

Now that you have things up and running, take some time having a play with the settings on the PM file and then running further "Updates". You will see how this basic portfolio tester quickly can quickly produce results. It's a nice change to managing a portfolio using MT4, as this can be a really time consuming process. It used to take me about 2 days to run a test over 40 instruments in MT4.......now it takes about 30 minutes using this Basic Module with 40 instruments in the portfolio.....a real time saver.....plus you have the ability to manipulate the resultant data and include further analytical options in any way shape or form your desire.

Fred has coded his models entirely in Visual Basic and tests them across the entire ASX 200 instrument list and punches out results in a jiffy. You perhaps can start seeing the powerful options we have under the Excel environment. For the moment however we will stick to the Basic Module.

Why not try this now you have the model up and running:
Why not "take a snap" of the "Trade Summary" screen to record the results for future comparison against different settings.

To do this ensure you have opened the PM file and you are on the "Trade Summary" worksheet. You should see the results of your last run on the screen in front if you.

Now click the "Take Snap" button and those results should be copied to the "Store Snaps" tab. Just check it is working. The snaps are jpeg images so you can move them around the "Store Snaps" worksheet to make room for new snaps at a later date.

Ok, now that you have recorded your prior test result let's change some of the variables in the PM "Trade Summary" worksheet. Refer to cells in bold yellow. If you change the content of these cells it will translate that info to the entire portfolio on your next update.

For example let's change the Donchian Entry to 100 and the Donchian Exit to 50.

Now after you have changed these inputs, then select "Update Data" and have a coffee and in about 5 mins, your next results will be ready. Take a snap of those results and then go to the "Store Snaps" worksheet to compare these result against your prior run. Note that you may need to move around the jpeg snaps as they do overlay on top of each other. You can also reduce or increase their size if you want.

Here is what you should end up with on the "Store Snaps" worksheet.



I then closely look at the key stats of each run and the equity curves and drawdowns to evaluate the result of these changes made to system variables.

This is how I tweak my systems as I prefer not to optimise variables but rather use logic as a basis to assess how the system would best perform. Anyway, we will get into optimising using "sensitivity testing" at a later date.

It's great you have things up and running now B. Next weekend *ooops my mistake*....on the weekend of 3 July we will get deeper into it all......as I will be out of range next weekend taking a chill pill. :-)

Regards

C

I hope your son feels better soon, C.. a dislocated shoulder is painful..

Thanks for taking the time to place all this info in the thread mate, I'll try and have a look this Week..

Hi C,
I ran a few different input values. I can copy and paste the image into a word file. Can the picture be exported directly to an image file like MT4 does?

The donchian channel indicator that you are using, is that programmed into xcel or do you get the values from MT4?

Can I use custom indicators in the spreadsheet too?

Thanks

Thanks Ja

No rush mate as I know you have a lot on your plate.

It would be good to get your feedback when you have a crack at getting it setup. Hopefully its a pretty seamless exercise.

C

Great stuff B

You nailed the performance of GOLD in your test. Have a look at it's contribution (highlighted by me) made to the portfolio of $281,753.17 for your test using your settings.



Despite the great result from gold, you might have issues with that equity curve and max draw of 43% over the period. That's why we play around with these settings or better still, when we have a good idea of how our simple Donchian system works, we use logic to set our variables to identify robust settings that have wide applicability across our investment universe. Over time, you get a handle on this approach.

If you go to the "Blending" worksheet in your PM file and select "1" for Gold and set all other instruments at "0" in row 1 then go back to the "Trade Summary" worksheet and select "F9" on your keyboard or "Recalculate", then you should be able to see the individual contribution made by GOLD and it's key statistics.

Have a play around with various combinations of instruments to see the combined results of the instruments you turn "off" or "on" and then recalculate to see it's effect.

We can do whatever we like in the excel environment. Later on we will show you how to create or record your own macro's or VB code so you have full flexibility on what you want to achieve with any outputs (such as jpeg images) delivered by the system.

The Donchian channel indicator is created from scratch in the TM file. When we get into the details of design in this thread, we will be creating a host of indicators within the Excel environment itself. The process of creating indicators from scratch itself is a useful exercise as it teaches you what is under the hood of each indicator and also highlights the strengths/weaknesses of each indicator and their specific application.

I have not yet come across an indicator that we cannot create from scratch in Excel and so far I have probably looked at 30 to 40 of them in the design of different systems used through this approach. As we progress down the track we will create each indicator from scratch and include examples in a resources directory that we can quickly apply to the TM file in our design process.

Hey C,

Just been downloading and setting up your files. So far so good, and I'm somewhat speechless at the moment by how splendid it all looks and how much work has gone into it. I mean, wow... just WOW! Even all the instructions and explanations so far must have taken an age. You don't do things by half, so you?

Anyway, I have a slight problem with my macros which is exasperated by the fact that I'm running it on a Japanese version of Excel, and my kanji skills aren't great. So I'll have to wait until the missus stops hogging the English PC so I can see what the problem is. They probably just need enabling, that's all. I'll let you know.

In the meantime, just a couple of very small things I've noticed which you may or may not want to fix:

1) In Post 32 I'm assuming that the ">PC", ">TM", ">AUDUSD", etc. are the files themselves, rather than folders of those names in which to put the files? It's probably obvious to normal people, but consistency freaks like me might benefit from the file extension after their names to differentiate them from folder names.

2) Post 46 has someone's name in the directory structure. Don't know who it is but you might have left it there by mistake?

That's all for now, sorry. To be honest I've not had time to digest the later explanations yet. Good job I'm not trading much this week! But very well done, mate
You deserve a big pat on the back for this.

Cheers NT :-)

Hopefully once you get onto your English PC it should be straight forward mate.

Thanks for the tip regarding file names with extensions. Hopefully I can still edit them (Damn.....too late) but if not, then I will include file extensions for the future to keep consistency. You are right in Post 32, those names (PC, PM, TM and the instruments AUDUSD etc that are all auto created) are the files with .xlsm extensions.

That name is 'yours truly' mate. No harm letting others know my alter ego. Copernicus might have been stretching things a bit. It's too cold in Poland and the monastery is just so miserable.

Looking forward to lots of your feedback like this NT as it really helps.

Ultimately I will develop a manual in pdf from these posts and will be able to make all the current issues disappear in the pdf.

Thanks once again NT....lot's of bear hugs for the debugs (poetry allowed) :-)

Some things to do while waiting for the next installment in how to create these models (due on the weekend 3 July 2016).

Guys

I thought I would give you some stuff to do to increase your familiarity with this system.

Let's use this system as a base to see if we can generate a result that is satisfactory for your risk profile.

I always generate snaps of my work and of others as I use this info to progressively hone my system to meet my objectives. For example below is a screenshot of stored snaps of different variations of this system. I also make notes in this sheet to show the logic I have applied to progressively hone the system. This is invaluable for future reference in system development. Note that I use logic for my decisions and do not rely on standard forms of optimisation of variables. Doing so ensures you manage your system and are fully aware of it's capabilities in responding to market conditions.



Let's drill down into these snaps one by one to demonstrate what I do. Why don't you run the same tests to replicate these results. Just change the variables and details on your "Trade Summary" worksheet of your PM.xlsm file and update the data and Bob's your Uncle.

Snap 1
I have run Blackstack's test myself to ensure I generate the same results as him. Fortunately if we use the same data, we can collaborate on projects like this and as a team, build very robust and powerful systems. Here is a snap of Blackstack's run with some notes. You can move your snaps around the worksheet by dragging and dropping the image files. Also you can create any notes you like in the worksheet just by typing them in. Keep this "Store Snaps" worksheet as your personal worksheet to honing your system results.



Note how I have added commentary for my own future reference.

Snap 2
Here is the initial run that you all should have generated using the downloaded files supplied by following the commentary of this thread. Note the commentary I have made regarding trade frequency which is a bit low to have any statistical significance. I obviously want to increase trade frequency if I can so I will be working on that in this demonstration. Note that there were a total 1347 long trade signals generated and 548 trades generated (refer to statistics details on the left of the chart). Because this is such a long term trader, I was only able to take 57 of these trades as the duration of each trade ran for extended periods of time.



Snap 3
In an attempt to increase trade frequency, I have reduced the Donchian entry settings to get more trades over the period. A good result is delivered in terms of Compound Annual Growth (CAGR) and Drawdown but despite the increase in trade frequency from 57 (previously) to 77 trades.....it's still too low.....so now I will look at measures to further up the ante.




Snap 4

Wooooooahh there poindexter!!!!!!! Great result but it also comes with a nasty drawdown of 40%. Trade frequency has increased nicely to 101 trades simply by reducing the ATR multiplier that applies to the initial stop on entry and also increase position size accordingly to reflect the $4000 trade risk. In the next test we need to dial down trade risk a bit to reduce that drawdown.



Snap 5

Ok.....so this (32%) is about what I am prepared to live with in drawdowns over the period to get that 8% CAGR. This is for training purposes....and the reality is that we would be honing things a bit more....but it gives you an idea. On my next test I will be further dialling down this outcome so that we can include this system as part of a future portfolio suite of systems that may include up to 10 separate systems all designed to iron out the rough spots of each individual system. It works on the principle using low correlated strategies that the "TOTAL is greater than the sum of it's individual parts".




Snap 6 - The finale
We have now significantly dialled down trade risk to $500 per trade. The result is that we have a CAGR contribution of 2.3% and a max draw of 7%. This is sufficient for our future purposes where we start blending portfolio together to get returns of >20% PA with drawdowns of say 20% by system consolidation. Once we have mastered this Basic Module I will also be providing a Portfolio system blending file where we start mixing up the pot by creating multiple system strategies.



Anyway guys....have a play using the Basic Module incorporating the simple Donchian breakout system to become familiar with it all.

Later on we can add further instruments to the portfolio and I will teach you how to do this.

I hope you guys are enjoying things so far. Let me know if I need to go over stuff again or can assist in any way. I am hoping that it is pretty self-explanatory...but when you become familiar with your system, you start taking things for granted......and I am hoping I am not losing any of you.

Cheers

C

PS. Guys, this model does not compound profits and adopts a standard $ or risk % to a fixed initial deposit. If you want to see what reinvesting profits does, we can simply include the reinvested equity curve and associated reinvested drawdown into the chart using the Monthly results worksheet where we compound results from the initial deposit. I don't include compounding or reinvesting profits in my tests however as I am seeking to find results that do not use the principle of compounding to exaggerate system performance. This can come later when we have bedded down some robust systems.

Bloody hell C, you're typing faster than I can read!

I've now got my macro problem sorted so I'll have a proper play with it between now and the weekend after next. And thank you for your earlier reply and kind words.
Until then... have a good one

Cheers NT. I was applying the lash to Mrs C to increase her pace. Her typing skills really need to improve.......not to mention her tractor skills.

Guys

I apologise.

I have some urgent matters to attend to and will be off the Forum for the next couple of weeks. When I return this thread will continue.

In the meantime, please become familiar with the system as discussed in the last few posts so we can progress quickly into the detail when I return.

All the best.

C

Take your time buddy.. FX will be there tomorrow as well
Guido

I managed to get the basic module running.

The only part that gave some issues is the path directories. The links in the PC file (row 6 and 7) should be updated first, before updating the macro path directory and pressing Update Data. I look forward to the coming updates and hopefully I can participate in the future. No reason to rush things. Gives me some time to learn VBA.

Regards,