Diversified Trend Trading – Excel Projects

Hi C,

Thanks for starting this thread. It's going to be a lot of hard work for you! But it will help many traders with their system analysis so well done

Personally I use NinjaTrader for my statistical analysis and backtesting, but it would be nice to approach it from a different angle sometimes to hopefully confirm what Ninja is telling me. It would also be nice to use different historical data to my own. Presumably you'll be telling us how to buy/obtain reliable minute and daily data in CSV form? Or even posting a sample? I can probably export mine from Ninja but it would be nice to complement it with independent data from time to time.

I guess one drawback with Excel is that you can't annotate the chart easily, which makes it difficult to visualise the historical trades a system would take. Maybe there are other ways to do this, I don't know! Or maybe we don't need to do this at all? But I'm jumping the gun here and we can discuss this much further down the road if needs be.

Best wishes,
NT

Excel... Yum.

Hi Guys

There is a vision with this project that is dependent on the ultimate skills of this community whereby under the Excel environment we seek to achieve an optimal outcome in terms of both rigorous backtesting (Excel), data storage and retrieval capability (Access or SQL) and ultimately linking with a live trader (Excel hybrid) that can access 'best rates' across multiple brokers and across asset classes.

Not that we will be copying the attached.....but it at least gives you an idea of where I might be trying to take this project with a live trade module that talks to a powerful backtesting model..



While it appears *dreaming* and perhaps beyond the scope of what we can achieve, I am quietly confident that given the skills of this community, something of this capacity can be created by this community with a lot less effort than you may think.

We just need to take things a step at a time and not bite off more than we can chew.

As a start we already have a working model for automated backtesting which I will be sharing that will give us a feel for the capabilities of Excel. It certainly can be streamlined more with some adept MS Excel skill work but at least it's a good start. For example, ultimately what we achieve on spreadsheet can be converted into Visual basic for streamlining purposes and ramping up data processing capability.....though it is nice to at least start with the spreadsheets we are all familiar with.

I am suss of commercially available products as I like to know exactly what is 'under the hood'. Also I have a bone to pick with the platforms out there given the need for reliance on 3rd party programmers and 'honest and transparent brokers' *coughs* to give a trader an edge. I would prefer that I learnt the skills to become totally reliant and be a master of my own destiny in this exceedingly competitive environment.

Hopefully many of you also want to take the same journey. There are already some very heavy duty skills lurking in this thread that make me confident that we can make this project work.....but even if it doesn't, we will learn valuable stuff along the way.

Another problem when you select a new platform is that you may only know or use 10% of it's total capability. With this project however, if we all learn the steps along the way, then we will not only have 100% skill capability with the finished product but we also will be sufficiently confident to adopt it as our own and take it to places others haven't even thought of.

NT you are right about the limited capabilities of Excel in the graphical charting space, but we do not need to abandon MT4 fully as it actually is a useful tool for charting purposes and it is free. We can cross check our excel rules against MT4 as a useful guide if we are 'visual people'. I actually find, once you get a bit of confidence in your excel capabilities that you don't need to use a graphical interface....though it is useful to at least start with it......anyway for the purposes of the training demo we will be using MT4 charts as a guide and cross-checking mechanism for our calcs.....but down the track.....some may just want to ditch it entirely. Also with regard to accessing data, that will be discussed in detail on how to do it and also we will be using standard data I supply for the demo project so that we are all on the same page and can cross-compare our results with fidelity.

In the attached video demo of the live trader, you can see how using API's that you can get the best of all worlds with MT4 and Excel. Perhaps this is the broad type of path we could take as a future project for those interested.

Cheers guys.

C

if it is a mr copernicus thread, I'm in !

Great stuff J

http://www.financemagnates.com/forex...-gain-clients/


i have spoken to the CEO and used this platform in the past, just thought id add my 2cents.

Cheers S. What grabs me is that Ernie Chan is onto it and I am familiar with his abilities. Thanks muchly for the link. I will look into it. Perhaps we can look at back-dooring into it at a later date with the portfolio backtesting solution.

C

PS Found this relating to FXOne

Guys

I will be posting content tomorrow evening on this thread. It's been a big week and I need to chill for a bit.

Stay tuned.

C

Could I make do with Excel 2010 ? That is all I have right now.

excel 2010 shouldn't have much difference with excel 2013 except in terms of visualizations.. So I think it wouldn't harm to use 2010 instead. If there are functions which are in excel 2013 and not in 2010, I can probably write an UDF function for u.. I'm no expert but I think I'm sufficiently proficient in the use of excel and is the founder of www.excel-applied.com
I won't say I definitely can help you, but as an excel ambassador like myself in the field of analytics, I definitely like this initiative and will help all I can.. =)

Thanks. That is very kind of you.

I will see how I will do with the project and if it will be worthwhile for me to upgrade to 2013.

Part 1

Ok guys here we go.

BASIC MODULE

The basic module which is the initial subject of this thread provides the key files required to create and back-test a small portfolio of instruments. We can build the size of the portfolio later to any number of instruments. Upon becoming familiar with how the system operates we can then progress to simplify and streamline the model further and also start testing a vast range of strategies to assess their robustness and applicability for live trading.

The key ambition of the Basic Module is to develop the confidence of users in Excel and create enthusiasm in the project.

Once we have mastered the Basic Module what can then be done is virtually limitless..so stick with it.

For the purposes of this Basic Module we will adopt the following conventions:

1. We will be backtesting on the Daily (D1) timeframe. We will be keeping things fairly simple for the moment.
2. Furthermore, to keep things simple to start we will only be adopting a single position for any trade signal generated and not be pyramiding or scaling into the trade for the moment.
3. We will be generating a trade signal on a particular daily price bar provided trade entry or exit conditions are met, and only entering or exiting a position on the subsequent open of the following bar if conditions from the prior signal still hold. There are a number of good reasons for adopting this premise. For a start:
   1. it ensures that we do not need to address intraday data as we are only after the open, high, low & close of a price bar. This way we can load up on long range data for backtesting purposes as it is only these 4 variables we can trust with any confidence in data supply from our broker sources;
   2. secondly, we tend to avoid any whipsaws associated with High Frequency Trading (HFT) activity during the course of a trading session;
   3. thirdly it prevents any errors occurring in our formulas associated with both a trade signal and an execution order arising on the same bar; and
   4. fourthly.and most importantly, the validity of this approach has been extensively tested by some trusted sources such as Cesar Alvarez who can demonstrate that in the long term, this is advantageous for our back-testing purposes. In managing risk what we need to remember is that the actual timing and method of entry does not need to be precise.however what does need to be precise is our trade management.

   
   
   
   

4. For the purposes of this demo project we will be testing 5 popular instruments on the Forex space as a portfolio (EURUSD, EURJPY, XAUUSD [spot gold], NZDJPY & AUDUSD). For future systems the number of instruments can be of any number given that each instrument is defined by a separate file which talks to each other by Visual Basic. Size of the portfolio is not a problem and the extent of the portfolio is virtually limitless. For example, the current portfolio I test with automated strategies in Excel comprises 40 different markets across a variety of different asset classes. If however the system becomes too complex in terms of trading rules and long winded formulas and code, speed of processing will slow down and you may find that you may ultimately test the data processing capability of your PC if you get too fancy.
5. Finally we will be testing over a 16 year timeframe. We won't for the purposes of this demo go for a longer period as to start with we wont be putting too much pressure on the processing capabilities of your PCs. Needless to say, if you want to progress down this path, the higher spec your PC capabilities the more crunching that can be done. The best way to find this out is to see how it goes. It should be fine but you may want to consider building your PCs processing capabilities if you want to get deep down and dirty with MS Excel and possibly MS Access (down the track).

Just an Important Note of Guidance Here: As a general guide when trading with a diversified portfolio, the quest for trading simplicity and an approach that avoids curve fitting should be a major objective. We need a standard simple approach that can be applied to any liquid instrument given that we intend on reaping the benefits of diversification.

Creating the System Folders
To start off with, we need to specify a directory structure on our PCs in which to store the files that will be used for backtesting purposes. Defining the File Structure from each separate system is a key prerequisite for this approach for the following reasons:

1. Having a standardised file reference structure is essential for standardised coding purposes which is coming later with a bit of Visual Basic;
2. Ensuring a standard file structure so that we all can easily navigate around each others projects; and
3. Keeping your work organised to assist future file reference.

At the base of the opening post of this thread you will find the following files for downloading. If you have already downloaded these files then it is best to do it again as they all have recently been updated.

Unpack all these files for a start into a single folder on your desktop. You will find that the files labelled TM, PC and PM contain single files whereas the Datahistory file contains 5 files of daily data that will be used for training purposes.

Now after you have all these files downloaded, it is time to create the directory structure for the system.

Each separate system (eg. Basic Module, System 2, System 3 etc.) are retained as a sub-directory and within each sub-directory, the key files for each system (PM, TC and PC) are contained. Additional instrument files for the portfolio will be created by the system through automation and included in this sub-directory.

The Data history is contained as a separate sub-directory which will be used by all systems developed by this back-test system.

I recommend the following adopted file structure (directory structure) for the project within which you store your files.



Now for the purposes of this Basic Module we do not have to worry about different systems. So to start with store the files that you have downloaded into the following locations on your PC.



Once this is done we are almost ready to run the system as a demonstration. Before we do however we need a bit more context to understand our system.

To be continued........

C

Part 2

Downloading Data History

While the description below demonstrates how to obtain data history to testing on your own, you have already been supplied with data history files that we will be using for our initial training. That way we will all be working with the same data.

So how do we go about obtaining our own data history?

Given that we are only after the open, high, low and close of each bar, then sourcing of reliable data is not going to be a problem. If we were defining our signals and entries/exits on the same bar using intra-bar data, then we would need to revert to a more reliable source of tick data such as that provided by Dukascopy via Tickstory….however the design of our system avoids the need for tick data.

While MT4 unfortunately does not utilise tick data in it’s method of timeframe data creation it does utilise valid control points (the open, high, low close) about which it the infills the range within these control points by random data. Provided we simply use the open, high, low and close from our brokers MT4 data history, we are not going to run into typical data error problems. So provided you can obtain open, high, low, close, and date and time data for this exercise that is all we are after.

The easiest source is of course your MT4 data history from your preferred broker. As we are using D1 data, this fortunately tends to be the most long range readily available data for any instrument in your brokers universe.

To export the data from your MT4 platform into your Datahistory subdirectory, follow the following steps.

1. On your MT4 platform, select Tools>History Centre
2. Then select the first instrument from the History Centre you wish to obtain data for….in this example….. AUDUSD. Then select the Daily (D1) data range. You should then be able to see the data with the most recent available daily data at the top of the list. In this instance it is daily data up to 17.06.2016. By using the scroll bar, just check the earliest data available which for Avatrade (the broker used in this example) was 23.08.1996. We therefore we have all the data we need to test over our defined backtest range of a bit over 16 years from 01.01.2000 to 31.05.2016.

 

1. Now select the “Export” function at the base of the history centre screen and it will ask you for a directory to save the data into. Select the Datahistory folder in your directory structure and accept the default data name given to it by MT4. The naming convention used by your broker is recommended as it is one less thing you have to worry about. For example, the naming convention for D1 data uses 1440 at the end of the instrument name to stipulate that the data is daily data. Select “save”. If you then navigate to your Datahistory folder you should find that that the file has been successfully exported to that directory as displayed below.

 

1. Now repeat this exercise for the remaining 4 instruments and you should end up with these files in your Datahistory.

Once you get into the swing of exporting data from your MT4 platform it is a pretty easy and effortless exercise……however as a future Excel project we should look into fully automating this function at the click of a button.

Note….it may be worth checking that there are no double ups in data etc.…as this can pose problems down the track. When you first commence this exercise, it is worthwhile spending some time just scrolling through the data to make sure that nothing unusual stands out like double up days etc.

With regards to volume data, this is also supplied in the Datahistory but not used for our backtesting purposes in this demonstration.

To be continued.......

C

Part 3

Description of Basic Module

Just by way of introducing the Basic Module I thought it might be worth explaining how to navigate around the system. Now remember the following description is simply an overview to familiarise yourselves with the system files. We plan to build these system files from scratch as a learning exercise so we can understand the detail later. This training will be undertaken over a number of weeks so the plan is to take things one step at a time. There is no harm however opening up the spreadsheets supplied to have a look at some of the worksheets and become familiar with navigating around them.

If you stuff something up.then dont worry.just download the files and start again.

The files you open are macro enabled so if you get a message about whether you want to enable the file, always select yes. Also if messages appear requesting links to update.also always select yes.

For our portfolio there are 3 critical files which possess different functions, namely:

1. Portfolio Manager (PM);
2. Trade Manager (TM); and
3. Portfolio Creator (PC).

​
PortfolioManager (PM)

Open up this spreadsheet which you should now have all downloaded. This spreadsheet file is where we define and store the values used for the strategy.
The PM spreadsheet comprises 7 different worksheets with their titles displayed at the base of the spreadsheet. We will become familiar with these all at a later stage.

Some immediate housekeeping:
Open up the PM file. Then select "Alt-F8" to bring up the Macro list. Highlight "CollectData" and then select "Edit".
At about the 7th row of code there will be a directory reference. Ensure that the reference directs you to the 'system files' on your PC. If not, then change the details and save and close the PM.

The relevant bit of code to check is highlighted in yellow.

Ok so now we are ready to have a look around the spreadsheet.

Trade Summary Worksheet
Select the Trade Summary worksheet. This is the main driver of the PM file where we define information that we want to test across the entire portfolio.



For example with our simple Donchian Breakout strategy, this is where we store the indicator values such as an entry when price is above/below the 150 period Donchian indicator and an exit when price is above/below the 75 period Donchian Indicator etc.

We also specify other variables such as the date range for testing. Note that we need to ensure that the date range is applicable to the date ranges of data history that we are using for testing.

All our key variables used for the strategy which is applied to the portfolio is stored in this worksheet and individual instrument files of the portfolio refer to this worksheet to adopt these settings and analyse results for that particular instrument.

The Trade Summary worksheet also displays a summary of results of the back-test of the portfolio consolidation including key statistics as well as an equity curve and drawdown chart.

We can augment this worksheet at a later date to include other useful tools and Performance summaries such as a Monte Carlo simulator or Sensitivity Test Optimizer (if optimising interests you..which I must state from the outset I avoid like the plague) as a fairly simple future Excel project.

Dont be daunted by the apparent complexity on this worksheet. We will learn to become familiar with how it all operates soon enough.

There is a button on this worksheet with the title Update Data. Once we have defined our values and have input details into the relevant yellow cells we are ready to commence testing. We then click on the Update Data button to commence the crunching of data across the portfolio to produce portfolio results which will update the results contained in all instrument files and this PM file.

Testing various scenarios comprehensively across the portfolio using the PM file is a pretty seamless exercise where you can select the inputs and then put your feet up and relax as the model crunches away.

Instrument Details Worksheet
The worksheet titled Instrument Details is where we store critical information from our brokers which is applied to instruments contained in our portfolio (our investment universe). The values in this worksheet link to each instrument file of the portfolio. When both the PM file and individual instrument files are both open, the instrument files are updated accordingly. Market data that we use for our portfolio include SWAP rate, tick size, tickvalue, spread, min and max lot sizes etc for each instrument.



Once you have clicked the Update Data button, this is when you get a cup of coffee as Excel crunches away and updates results.

Dump Sheet
This worksheet is automatically populated during the update process by the results of each instrument file in the portfolio. After your cup of coffee when the crunching has been done and Excel is idle, have a look at this worksheet. It simply contains data in columns for each instrument in the portfolio which is then used for consolidation purposes in the portfolio.



Blending
This worksheet simply represents the information from the Dump Sheet but includes an on/off function at the top of the screen for each column. If you type a 1 in the column header for each instrument, then it includes those instruments in the portfolio consolidation. If you type a 0 in the column, then it excludes that instruments results from the portfolio consolidation.

This is simply a quick measure the assess the impacts of including or excluding instruments from portfolio results.

Store Snaps
This worksheet is where jpeg images of the Trade Summary worksheet are stored. This allows you to store results for future comparison against alternate back-tests. To populate this worksheet simply refer to the Trade Summary worksheet and click on the button Take Snap and an image of the screen will be deposited into the Store Snaps worksheet.

Monthly Results & Annual Results
These two worksheets simply house monthly and annual results of the test run which can be incorporated into additional analytical features such as determining the Sharpe Ratio of the system etc.

To be continued......

C

Part 4

Trade Manager (TM)

This file is used as a template that is copied for each instrument in the portfolio by automation using the Portfolio Creator (PC) file.

With a portfolio of 5 different markets, the PC file will copy the TM file 5 times and update each file with datahistory and critical information drawn from the PM file. Each file will be automatically named as a discreet instrument of the portfolio.

The TM file is the workhorse of the system files and contains all the formulas required to generate and analyse trading results for an individual instrument in the portfolio.

Now open the file but also note that you also need to open the PM file after you open the TM file as there are links to both files that need to be activated. After you have both files open, press F9 to auto-calculate just to ensure it is updating. If messages appear to update then always select yes.

Trade Summary
Click on the trade summary worksheet at the base of the spreadsheet and you should be presented with the following screen.



Note that the TM template is automatically configured for AUDUSD. This entire file will be used for replicating purposes when we want to create the portfolio.

There is no information to input in this screen as all the information is derived from the PM file which is linked to this file.

Key statistics and an equity curve with realised and unrealised lines including a drawdown graph are provided on this screen for AUDUSD.

The Take Snap button on this worksheet simply takes a jpeg of the screen and loads it into the Store Snaps worksheet for comparative reference purposes.


Store Snaps
Where screen shots of various back-tests for this instrument are located.

Trade Results
This is where trade details are stored from a single back test run for the particular instrument.



Strategy Definition
This is the major worksheet of the file where all the crunching is done. We will be stepping through this in detail so dont stress about its content. This is where we define the rules of the strategy and also include the detailed calculations necessary to calculate trade details for the instrument.

Data Dump
This is a worksheet that contains data history which is automatically loaded into this worksheet from the Datahistory folder.

To be Continued......


C

Part 5

Portfolio Creator (PC)

This system file generates the portfolio by copying the TM file and creating a separate file for each instrument. Under automation it also updates the relevant data file to include the most recently available data history.

Open up this file to have a look at what’s inside.

It is a deceptively simple file that comprises two worksheets.

Include
The include worksheet simply lists the instruments of the portfolio in the same order as that detailed in the PM file dumpsheet.

Inputs
The inputs worksheet specifies the locations of where the portfolio files and the data history files reside.

It also includes a button that once clicked, generate the individual instrument files of the portfolio by copying the TM file and loading each instrument file with their respective data history.



Pay particular attention to the location details. You will need to ensure that these details reflect the locations of these files on your PC. If you have taken note of the instructions detailed in the section titled “Creating System Folders” the mapping of the file locations should be fairly straight forward.

Have a go at specifying the locations in the directory structure in rows 6 and 7 of this worksheet.

To be continued.....


C

Part 6

System Test

Ok..so we should be ready to have a first trial of the system. Lets see how we go. Make sure that macros are enabled if messages ask you.

Step 1.

Open up the PC file.

Ensure the location details are specified correctly. The end of each location directory should not change but how the directory structure begins may alter.

Click the button "Create Portfolio".

What should happen next is that the individual instrument files of the portfolio are generated and automatically loaded in your system directory.

Take note of any error messages and make sure you have the correct directory structure. Hopefully no error messages will appear.

Once Excel has stopped working then check your directory folders and see if 5 different instrument files are now sitting in your directory.

If yes.then you are ready to move onto Step 2.


Step 2.

Close down all files and save if asked.

Open up the PM file on its own.

Go to the Trade Summary worksheet.

Click the Update Data button.

The system then performs it's routines while you twiddle your fingers and get a cup of coffee.

Dependent on the speed of your machine, it may be a quick or slow exercise. You should find with a standard machine that it will take between 5mins to 10 mins to crunch.

Once excel has stopped working you should be presented with the results of the update on the Trade Summary worksheet of the portfolio.

Congratulations are in order if you get this far. That means that you can now play with the settings on the Trade Summary worksheet and do other different runs and become familiar with the testing process.

Take your time to become familiar with these files as they are the bread and butter for what is to come.

I or RedLineFred (Fred) should be able to field simple queries this week but I will have to take a rain check next weekend as will be away for a bit of R&R but will return on the weekend of 2/3 July to start the tutorial process on how to develop these files from scratch.

Please also give me and Fred an indication if you are finding the explanations too difficult..so we can slow down or focus on those areas that you are having problems with.

That's it for the content this weekend. I will field any queries tomorrow arvo.

Cheers

C

After I clicked the Update Data button it ran. Then there was a debug error where I needed to change the directory path. And I needed to move the 5 currency pair spreadsheet files to the directory it wanted to see them in.

There are a lot of #DIV/0! and #VALUE! in the cells of the Data Dump worksheet.

Thanks

Hi BlackStack...and this goes for everyone as well!

Well done for getting things so far.

Check the following:

Open up the PM file. Then select "Alt-F8" to bring up the Macro list. Highlight "CollectData" and then select "Edit".

At about the 7th row of code there will be a directory reference. Ensure that the reference directs you to the 'system files' on your PC. If not, then change the details and save and close the PM and try the update again by clicking on the "Update Button".

The relevant bit of code to check is highlighted in yellow.



Will be at footy for the next few hours but will return and assist if you are still having problems.

Cheers

C

far away this is the biggest and most important input ever made here at forex factory >>> FREE or COMMERCIAL <<< by any other user sice this forum created