Linear Forecast in Excel

Linear Forecast in Excel

13 November 2018, 10:06
Qoyyuum Kadir

I kept thinking about Linear Regression and how to formulate it into MQL5. My math skill isn't very strong, and yet I scored enough to pass through my exams (Thank god). As I understood, Linear Regression helps you find the next new value with the formula y = a + bx. Now I wasn't sure what values to put in place for a, b or x so I did everything as a trial and error. This is what I have found.

The FORECAST.LINEAR() function in Excel already has this formulated into a spreadsheet function where it takes 3 values.

=FORECAST.LINEAR(x, known_y’s, known_x’s)

In Forex context, I assumed that X is the Day or Time. So therefore, the known_x's are a list of days/times. The known_y's is the historical prices that would lead up to the new price, y. So I started a simple experiment without too much complexity in grabbing any Broker API or building a ready Expert Advisor on this hypothesis.

Still using Excel, I kept it simple. I made a little template that would help automatically determine if the new predicted price value is higher or lower than the previous price value. At first I struggled which price to enter. There's a bunch of numbers in each tick including, Open Price, High Price, Low Price, Close Price, Volume, or even a mix of any of these in median, mean, or otherwise. So I settled with Close Price following the Line Graph in my terminal.


I set Column A as just an index number of all the Close Price data that will be entered in Column B. Column C is the FORECAST.LINEAR function that would help determine the next predicted value. However, the problem with the linear forecast it doesn't work after a few values, so I thought getting 10 Prices to start data population before actual trading. At D11 would be the Expected result by a simple IF statement as =IF(B11>C12,"Down", "Up"). Which would also lead up to the column next to it for Actual result with another IF statement as =IF(B11>B12,"Down","Up"). Then compare between Expected and Actual result to see if the linear was accurate.

Above all of that, I did a little statistic counts of how many "Yes"s and "No"s and put that into a percentage, just like a strategy tester. I could add more like, frequency of "Yes"s and "No"s to see a streak of winning and losing but that comes a little later.

Math.Random() values

I'm a web developer and have a Chrome Console opened. I thought I could just use Math.random() to give me random values. With the screenshot above, I was a little surprised at the accuracy but the line in the graph looks very horizontal. This was most probably because

  1. I entered 2 digit values (except for that value 100, oops)
  2. Minimum and Maximum values are already established each time I run Math.random(), I take the hundredth digits and round it up.

So its not exactly a fair test but the experiment was interesting. I ran this twice and at both times, I get 70% accuracy.

My next theory is if I can do this with actual data from my broker. I popped up my terminal, opened up EURUSD H1 chart and started a fresh sheet with the same template.

Close to half accuracy

Doesn't look to promising. This chart was just entered today at around 16:10 on 13/11/2018 (GMT+8) with only 40 data points entered. Looks interesting still but if I add more functions to this excel while still copying data into it for further analysis, we might be able to figure out an actual formula for sustainable wins. Its last predicted tick value was that its going down, so I might try that and wait for the next tick to see if I make a quick win today.

Validated Linear Regression with a small account

I waited until 17:00 for the new tick and there it is. I entered a little late at 1.12396 and closed the trade at 1.12590 right after the formation of the new tick. That's enough trading for today and that sort of validates my hypothesis for a linear regression model trading. This is not to say that we should be trading with an Excel sheet but it does seem to work just this one time. This was an experiment to test this idea out. As always trade at your own risk.

Share it with friends: