Forex Collection Euronis
 
 
 

Calculating support and resistance levels in excel

Calculating support and resistance levels in excel

Still have a question? Ask your own!

Do you have access to historical data? If so, you can analyze it over a period of time for SLOPE, MIN and MAX. If the SLOPE is close to zero, then your MIN and MAX are points of support and resistance. You can also COUNTIF how many times the MIN or MAX was reached (within a given tolerance).

If you have access only to the current exchange prices, then you will need to store your own historical data over the designated period. You can use a bucket brigade (Copy and Paste) to shift the data into a range of cells with the latest (and pre-specified) number of minutes of data. In so doing, you drop the oldest point and add the latest. Or you can keep the streaming data until your worksheet fills up.

I strongly urge you to use VBA to drive your calcs. This is because worksheet formulas are always updating. So if you want to trap two or more highs, the MAX function will only get the highest value. VBA, on the other hand, can identify a relative high an hour ago, an absolute high two hours ago and another relative high four hours ago.

Unless you smooth the daylights out of the streaming data, you will find first and second derivatives to be driven by the trade by trade “noise” in your data rather than the trends. And if you do smooth the daylights out of your streaming data, you may find that any trends you find have lagged real-time to such an extent that your conclusion is no longer profitably tradeable.

Note that any time period you pick to analyze data over is subject to being the wrong one to use. And by “wrong” I mean that you are tempted to make unprofitable trades programmatically.

Bottom line: what you can easily see with the naked eye is almost impossible to do with Excel formulas, and merely difficult with VBA code. There’s a reason why the quants on Wall St get the big bucks.

Brokers | VPS | Signals | Articles