platinyumnum
Junior member
- Messages
- 15
- Likes
- 2
DISCLAIMER: Use the below, spreadsheet info, at your own risk.
What’s it look like to set Excel up to “crunch-the-numbers?” Here is a little example using Alpari’s historical data for the EUR/USD pair, hourly periods.
First-off, let me say that this is NOT any kind of automatic trading system. I am posting it simply to show anybody who is interested, what “programming” Excel is like. If you do your own Excel programming, you need to be careful that you don’t go into the future to get results. What I mean is, it is easy to accidentally use some of the next period’s figures in calculating what happened in this period. That’s like looking into the future. If we could do it, we would all be rich off the Forex, right?
This is a simple, practical, useable spreadsheet to show what could have happened if:
1. The currency price direction during each period were to determine a Buy or Sell at the Close of the period;
2. Based on the Price difference between this Close and last Close being within a certain range.
If you set your Excel spreadsheet up like this, and add your own historical data figures, you can get a little idea of what might have happened over the time period of your historical data, if you had done it like the spreadsheet dictates.
The first screenshot shows the program the way it looks, results-wise.
In cell A1 you can see that the figures I used were the Euro / USD pair, taken from Alpari historical data.
Cell F1 is the spread my broker uses.
Cells A4 through F4 are self-explanatory. One little clarification about the HIGH, LOW, and CLOSE (I usually drop the “E” off “CLOSE” to save space.). I always take the data and multiply it by 10,000 (100 for JPY pairs) right at the start, for ease of use. Besides, this gives me the dollar amount of the Price change or difference - column G.
Note cells F5 and F6. The difference between these 2 Closes is 18. If I had one contract (not 1 lot) riding on the amount in F5, I would have either gained $18 or lost $18 by the next time period Close, F6 (excluding spread and premiums or broker fees, if any).
Column G, again, is the difference between Period Closes. I have simply subtracted the former period Close from the current Close to get column G.
Column H is the Absolute Value of column G. All Absolute Value does is to turn all numbers into positive numbers; no more negative numbers. I use the Absolute Value as a limiting figure, sort of like part of a “ZigZag” system, to determine when I want to Open a new transaction.
Column I is “IT”. This is where I place the parameters that determine if and when I Buy or Sell.
This whole thing is quite simple. No Limits or Stops have been added outside the reversals caused in column I.
Column J is where I “store” a number when I start a new transaction. Let’s say I Open a new transaction by Selling at 1.2831 (12831 on my column F). Column J would hold the 12831 until such a time that column I indicated I should Close the transaction. This way I have the 12831 figure right where I can use it at the time I Close it out.
Column K is the PL = P/L = Profit / Loss. It takes into account the spread, but nothing else.
Column L is a running balance as profit and loss is added. Column L is based on a $500 starting balance, and only 1 contract per transaction. Usually I do a minimum of 1 contract per $1,000 of equity in my account.
Column M is a simple way to bring the final balance to the top. I use it on spreadsheets where I know that I am going to add more figures - extend the spreadsheet - at a later date. This spreadsheet wouldn’t really need it. But for calculating purposes ...
Cell I1 must have either a “1” or a “-1” in it. I use this to automatically change all the Buys, to Sells, and all the Sells to Buys. This comes in handy, at times, where the calculations I am doing fluctuate rather wildly. This spreadsheet doesn’t really need them. But you might have historical datum that make having this option available, something desirable.
Cell I2 is a limiting number. If the Absolute Value in column H is greater than this number, no transaction will be done this time period Close.
Cell I3 is a limiting number. If the Absolute Value in column H is less than this number, no transaction will be done this time period Close.
Cells K2 and L2 are the largest numbers in the K and L columns, respectively.
Cells K3 and L3 are the smallest numbers in the K and L columns, respectively.
The example I used in the pictures below show what might have happened if somebody had used this spreadsheet exactly as presented, every hourly time period since October 22, last year, with Alpari account, of course.
And, since I make my share of mistakes, if you find mistakes in the formulas, you are welcome to post.
http://www.trade2win.com/boards/attachment.php?attachmentid=56766&stc=1&d=1242463593
http://www.trade2win.com/boards/attachment.php?attachmentid=56768&stc=1&d=1242463593
http://www.trade2win.com/boards/attachment.php?attachmentid=56770&stc=1&d=1242463593
http://www.trade2win.com/boards/attachment.php?attachmentid=56772&stc=1&d=1242463593
What’s it look like to set Excel up to “crunch-the-numbers?” Here is a little example using Alpari’s historical data for the EUR/USD pair, hourly periods.
First-off, let me say that this is NOT any kind of automatic trading system. I am posting it simply to show anybody who is interested, what “programming” Excel is like. If you do your own Excel programming, you need to be careful that you don’t go into the future to get results. What I mean is, it is easy to accidentally use some of the next period’s figures in calculating what happened in this period. That’s like looking into the future. If we could do it, we would all be rich off the Forex, right?
This is a simple, practical, useable spreadsheet to show what could have happened if:
1. The currency price direction during each period were to determine a Buy or Sell at the Close of the period;
2. Based on the Price difference between this Close and last Close being within a certain range.
If you set your Excel spreadsheet up like this, and add your own historical data figures, you can get a little idea of what might have happened over the time period of your historical data, if you had done it like the spreadsheet dictates.
The first screenshot shows the program the way it looks, results-wise.
In cell A1 you can see that the figures I used were the Euro / USD pair, taken from Alpari historical data.
Cell F1 is the spread my broker uses.
Cells A4 through F4 are self-explanatory. One little clarification about the HIGH, LOW, and CLOSE (I usually drop the “E” off “CLOSE” to save space.). I always take the data and multiply it by 10,000 (100 for JPY pairs) right at the start, for ease of use. Besides, this gives me the dollar amount of the Price change or difference - column G.
Note cells F5 and F6. The difference between these 2 Closes is 18. If I had one contract (not 1 lot) riding on the amount in F5, I would have either gained $18 or lost $18 by the next time period Close, F6 (excluding spread and premiums or broker fees, if any).
Column G, again, is the difference between Period Closes. I have simply subtracted the former period Close from the current Close to get column G.
Column H is the Absolute Value of column G. All Absolute Value does is to turn all numbers into positive numbers; no more negative numbers. I use the Absolute Value as a limiting figure, sort of like part of a “ZigZag” system, to determine when I want to Open a new transaction.
Column I is “IT”. This is where I place the parameters that determine if and when I Buy or Sell.
This whole thing is quite simple. No Limits or Stops have been added outside the reversals caused in column I.
Column J is where I “store” a number when I start a new transaction. Let’s say I Open a new transaction by Selling at 1.2831 (12831 on my column F). Column J would hold the 12831 until such a time that column I indicated I should Close the transaction. This way I have the 12831 figure right where I can use it at the time I Close it out.
Column K is the PL = P/L = Profit / Loss. It takes into account the spread, but nothing else.
Column L is a running balance as profit and loss is added. Column L is based on a $500 starting balance, and only 1 contract per transaction. Usually I do a minimum of 1 contract per $1,000 of equity in my account.
Column M is a simple way to bring the final balance to the top. I use it on spreadsheets where I know that I am going to add more figures - extend the spreadsheet - at a later date. This spreadsheet wouldn’t really need it. But for calculating purposes ...
Cell I1 must have either a “1” or a “-1” in it. I use this to automatically change all the Buys, to Sells, and all the Sells to Buys. This comes in handy, at times, where the calculations I am doing fluctuate rather wildly. This spreadsheet doesn’t really need them. But you might have historical datum that make having this option available, something desirable.
Cell I2 is a limiting number. If the Absolute Value in column H is greater than this number, no transaction will be done this time period Close.
Cell I3 is a limiting number. If the Absolute Value in column H is less than this number, no transaction will be done this time period Close.
Cells K2 and L2 are the largest numbers in the K and L columns, respectively.
Cells K3 and L3 are the smallest numbers in the K and L columns, respectively.
The example I used in the pictures below show what might have happened if somebody had used this spreadsheet exactly as presented, every hourly time period since October 22, last year, with Alpari account, of course.
And, since I make my share of mistakes, if you find mistakes in the formulas, you are welcome to post.
http://www.trade2win.com/boards/attachment.php?attachmentid=56766&stc=1&d=1242463593
http://www.trade2win.com/boards/attachment.php?attachmentid=56768&stc=1&d=1242463593
http://www.trade2win.com/boards/attachment.php?attachmentid=56770&stc=1&d=1242463593
http://www.trade2win.com/boards/attachment.php?attachmentid=56772&stc=1&d=1242463593
