Directional Movement - Backtest Results and Implementation Issues

Directional Movement - Backtest Results and Implementation Issues
Discusses basic issues and results when attempting to back-test the Directional Movement formula.

Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec
Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)
VBA Code: Method B of Code
Excel Version: 2010

Note: 1) As with the Aroon Indicator, the Directional Movement can be used for other time frames and not necessarily daily data as is the context when I wrote the Directional Movement explanation page.

2) The directional movement indicator refers to an implementation of the directional movement formula. What this page attempts to do is to backtest the directional movement formula, not the directional movement index and also not the directional movement indicator. Return to the technical analysis page for links to the respective indicators, codes and backtest results. 

Click here for background information, formula, calculation steps and the entire VBA code.

There are multiple variations on how to apply an indicator. I seek your understanding that the work done below are experiments and are for information purposes only.

Results Summary
Divergence does not signify that a trend is forming in the hourly charts based on my dataset. I inverted this trading rule and arrived with a strategy that is profitable before transaction costs, as expected. Given the large number of trades generated, the naive trading rule applied will most likely not be profitable after costs. The interesting fact is that this is the second or third indicator I found where the  usual trading rule I read from other sources has to be inverted in order for the strategy to even show before transaction cost profitability.

Procedure in Brief

Pasted the code into Excel with changes made as per comments in code to direct application to ranges with input data (High, Low, Close columns). Screen Shot 1 shows the output (the last four columns).

Screen Shot 1

Detailed Findings and Results

Summary of Trading Rules -

1) Calculate Positive DM Minus Negative DM. Recall that Negative DM is a positive value. Negative refers to the fact that a new low was created in the current bar. 

2) If (Positive DM Minus Negative DM) is greater than 0, Long 

3) If (Positive DM Minus Negative DM) is lower than 0, Short

4) Else maintain long or short position

517 index points of profit (before transaction costs) were generated on a large set of 6,687 trades. Average return is very small at just 0.08 points per trade, which means that such an application will not cover trading costs for most people (I guess?). 59% of trades were profitable. Standard deviation of returns for each trade is 4.93 points. The profit trajectory of this indicator and trading rules is shown in Chart 1. 

Chart 1: Cumulative Profit of Strategy (Before Costs)

Conclusion

Although this application of the DM generated a lot of trades, just like my attempt with the Aroon Indicator, the trading rule in this case did not generate statistically significant profitability based on the student t statistic. The low per trade profitability of this approach negated the statistical effect of a large number of trades in reducing standard error. I end this post at this juncture as we will be using this Directional Movement formula in various forms when backtesting the directional movement index and the directional movement indicator. More can be elaborated then. 

Any suggestions or comments are welcome. 

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Aroon Indicator / Oscillator - Backtest Results and Implementation Issues

Aroon Indicator / Oscillator AI / AO - Backtest Results and Implementation Issues 
Discusses basic issues and results when attempting to back-test the AI and the AO indicators

Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec
Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)
VBA Code: Method B of Code
Excel Version: 2010

Note: When I wrote the post for the Aroon Indicator VBA code, I wrote it in the context as though the Aroon Indicator and Oscillator can only be used on a daily basis. This is not necessarily always the case. In this case, I used the Aroon Indicator on the same hourly data as with all other backtests. 

Click here for background information, formula, calculation steps and the entire VBA code.

There are multiple variations on how to apply an indicator. I seek your understanding that the work done below are experiments and are for information purposes only.

Summary of Results 

While the Aroon Indicator has been described as a "trend" type indicator, I found that it behaves more as a price reversion indicator in the context of hourly data. Like the adaptive price zone (APZ) indicator, commonly known applications of the Aroon Indicator turned out to be loss making, requiring an inversion of the trading rule. While the Aroon Indicator (and the final trading rules used) is profitable before transaction costs, the key challenge lies with tweaking it to be profitable AFTER costs. Average profit per trade is only 0.2 index points per trade. 

Procedure in Brief

Pasted the code into Excel with changes made as per comments in code to direct application to ranges with input data (High, Low, Close columns). Screen Shot 1 shows the output (the last two columns).  

Screen Shot 1

Detailed Findings and Results
Period is set at 5 - This means that the formula looks at only the last five hours of data in the calculation of current hour Aroon Indicator value.

Summary of Trading Rules -

1) If Aroon Up is larger than Aroon Down at the end of each hour, SHORT in the next hour
2) If Aroon Down is larger than Aroon Up at the end of each hour, LONG in the next hour.
3) Otherwise, carry forward previous hour's position.

This set of trading rules generated 869.5 index points of profit (before transaction costs) on a large set of 4,146 trades. Average return per trade is 0.210 points on a standard deviation of 6.328 points. The success rate of this strategy is very high. 63.9% of the trades were profitable (before transaction costs and slippage). The profit trajectory of this indicator and trading rules is shown in Chart 1.

Chart 1: Aroon Indicator Cumulative Profit (Before Costs)

Conclusion

This set of trading rules and the indicator used generated a lot of trades. As a result, the standard error of the mean return is very small due to the formula used, which is standard deviation of returns of each trade divided by the number of trades. In turn, standard statistical distributions, such as the student t-statistic and normal z-statistic are significant when computed for this strategy. 

However, I have my doubts over the significance of these statistics. Due to the long tail nature of financial data, standard distributions may not correctly present the distribution of the returns of this strategy. Maybe this statistical significance will disappear when the correct statistic is used. 

Furthermore, I have repeatedly emphasized that the returns I calculated exclude commissions and slippage. I believe that this naive strategy on its own may not be profitable once I incorporate these costs.

The Aroon Oscillator measures the difference between the Aroon Up and Aroon Down indicator. As such, my trading rules are a form of the application of the Aroon Oscillator. 

Further research, in my opinion, can be in the application of these naive trading rules to larger time frames, e.g. daily, half-daily charts, which are more likely to generate less trades and lower trading costs. However, at larger time frames, the behaviour between price movements and the Aroon Indicator may change and may require the trading rules to be adjusted.

Any suggestions or comments are welcome.

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Adaptive Price Zone APZ - Backtest Results and Implementation Issues

Adaptive Price Zone APZ - Backtest Results and Implementation Issues 

Discusses basic issues and results when attempting to back-test the APZ indicator

Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec
Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)
VBA Code: Method B of Code
Excel Version: 2010

Click here for background information, formula, calculation steps and the entire VBA code.

There are multiple variations on how to apply an indicator. I seek your understanding that the work done below are experiments and are for information purposes only.

Summary of Results 

Conventional application of the APZ (i.e. long at the lower boundary and short at the upper boundary in anticipation of price reversal) is found to be not profitable. However, the reverse is somewhat profitable (long when price exceeds upper boundary of APZ and vice versa), suggesting that this technical indicator and the applied trading rules have potential for real-life implementation with further tweaking to the application method (e.g. chart indicator based on hourly data, but trades are generated as and when the trading rules are met subsequently). 

Procedure in Brief

Pasted the code into Excel with changes made as per comments in code to direct application to ranges with input data (High, Low, Close columns). Screen Shot 1 shows the output (the last seven columns). The first value of some columns are error values as their formulas include reference to cells on the first row which are actually text headings, not numbers. However, calculations in subsequent rows are unaffected. 

Screen Shot 1

Detailed Findings and Results
Inputs - Band factor is set at 2, EMA period is set at 5

Summary of Trading Rules -

1) If Close at this hour is higher than the APZ upper limit at the end of the last hour, enter long at the end of this hour
1) If Close at this hour is lower than the APZ lower limit at the end of the last hour, enter long at the end of this hour

223.25 index points of profit (before transaction costs) were generated out of this strategy on 344 trades. Average return per trade is 0.65 points on a standard deviation of 21.34 points. Therefore, this strategy does not look statistically profitable. The success rate of this strategy is 39%, i.e. only 135 trades were profitable. The profit trajectory of this indicator and strategy is shown in Chart 1. 

Chart 1: Cumulative Profit of APZ and Applied Trading Rules

Conclusion
Contemporary price behavior for the S&P500 suggests that the APZ is more suited for use as a "trend" type indicator, rather than a "price reversion" indicator as I have always thought. The key risk is that we cannot be certain that the behaviour of the APZ indicator versus the S&P500 will remain constant in the future. Under certain regimes, the APZ may behave as a price reversion indicator which would require the inversion of the above trading rules.

Further research can be in the application of this model in finer time granularity.

Any suggestions or comments are welcome.

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Accumulation/Distribution Line ADL - Backtest Results and Implementation Issues

Accumulation/Distribution Line (ADL) - Backtest Results and Implementation Issues

Discusses basic issues and results when attempting to back-test the ADL line indicator

Data: S&P500 Emini Futures
Futures Contract Rollover: Arbitrarily set on second Friday of every Mar, Jun, Sep and Dec

Period: 28 Dec 2008 - 31 Mar 2012 (Hourly Chart)

VBA Code: Method B of Code

Excel Version: 2010

Click here for background information, formula, calculation steps and the entire VBA code.

There are multiple variations on how to apply an indicator. I seek your understanding that the work done below are experiments and are for information purposes only.

Summary of Results 
A naive strategy produced profitable results without accounting for transactions costs. Further research is necessary to create expected returns that remain positive after trading costs.

Procedure in Brief

Pasted the code into Excel with changes made as per comments in code to direct application to ranges with input data (High, Low, Close columns). Screen Shot 1 shows the output (the last three columns)

Screen Shot 1

Detailed Findings and Results

1) Conventional implementations of the ADL line suggests that divergence between ADL and prices are meaningful. However, it is difficult to judge divergence based on indicator and price levels alone. 

In my sample period, the ADL line, like price data, is non-stationary. Simply put, the ADL does not fluctuate around a constant mean. Otherwise, I simply did not use enough data to demonstrate the stationarity of the ADL line. Therefore, the ADL line in its raw form may not be easy to interpret. 

Chart 1: ADL line versus S&P 500

2) I tried rescaling the data using the formula -

Adjusted Close = (Close - Low of entire period) / (High of entire period - Low of entire period)
Adjusted ADL = (ADL - ADL Low of entire period) / (ADL High of entire period - ADL Low of entire period)
Chart 2 shows how the rescaled data looks like

Chart 2

Again, there is no meaningful interpretation at first glance. Furthermore, in real life implementation, there is no way of knowing the low and high of the entire period, including past, current and future moments in time.

3) Finally, I resorted to measuring the change in

I used the following simple trading rule

A) Calculate the percentage change in price at the end of each hour
B) Calculate the percentage change in ADL at the end of each hour
C) Calculate Percentage Change in ADL - Percentage Change in Price
D) If Percentage Change in ADL is higher than Percentage Change in Price, Long (in the next hour)
E) If Percentage Change in ADL is lower than Percentage Change in Price, Short (in the next hour)
F) If Percentage Change in ADL is equal to Percentage Change in Price, Maintain Position (in the next hour)

Conclusion
Unadjusted for slippage and transaction costs, my simple trading rule above generated 9,838 trades over a three year plus period and 601 index points per contract of profit. Chart 3 shows the cumulative profit of this strategy (without transaction costs).

Chart 3: Cumulative Gross Profit

However, the sure number of trades suggest that transaction costs will be high. As such, practical implementation will be a challenge. The breakeven level requires each trade to have a transaction cost of at most 0.061 index points (601 divided by 9838).

Further research can be in the modification of trading rules, such as enter into a long or short position only if the positive or negative divergences are sufficiently large.

Any suggestion or comments are welcome.

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Understanding Technical Indicators: Filters in Finance

One of the first few questions that I ask myself is what are technical indicators? Are they just a jumble of equations? In fact, most technical indicators are filters. A filter can be viewed as an equation that transforms a time series to another form, such as how moving averages are supposed to smooth a time series. The objective of a filter is to identify and extract certain features, such as the trend and cycles, from a time series. Filters are commonly used in most forms of analysis, such as in engineering, signals processing, image recognition, economics and finance. Hence, I cannot do justice without doing an article on filters. Even some of the more sophisticated models rely on effective filtering to work.

What a Filter does

In simple terms, a filter changes x to a different series y because you think that y makes it easier for you to analyze x, e.g. observe the trend in x. You can visualize this operation using the diagram below.

Filters can be classified into linear and non-linear types. There are other classifications such as infinite/finite impulse response and causal/non-causal filters which will be elaborated later. Whether an equation is linear or nonlinear is a mathematical construct. You can view the more rigorous explanations at the references below. Intuitively, a linear equation must obey the following rules

[Equations are typically represented by functions. For instance, f(x)=1 can refer to the equationx+2=1 or g(xy)=20 can refer to xy-9=20.]

Nonlinear equations do not obey the two rules above. The intuitive explanation of a nonlinear equation is one where changes in its input do not lead to a proportionate change in its output. An example of a nonlinear equation will be x²+x=2. A 50% change in x to 1.5 will cause x²+x=3.75, a 87.5% change.

Hence, it becomes obvious that x can be changed or mapped to y via functions that are either linear or nonlinear. The simple moving average, such as SMA3 below is an example of a linear filter, using n to denote the filter period or the number of historical periods this indicator averages.

The SMA3 of 1,2 and 3 is 2. If we increase 1, 2 and 3 respectively by 50% to 1.5, 3 and 4.5, SMA3 becomes 3, an equivalent increase of 50%. An example of a nonlinear function is a neural network whose explanation requires a separate post in itself.

Causal filters utilize the past and current values in mapping inputs to their outputs, like most technical indicators. Non-causal filters utilize both past and future values. An example will be a centered moving average.

As future values are unobservable in real life, non-causal filters are more for analyzing and understanding current data than for predicting future values.

Types of Filter outputs

Filters are also classified based on their outputs into finite and infinite impulse response filters. An impulse response is a filter’s output to an unit impulse signal. Consider a “magical” option that costs $1 only on today and nothing on all other days, both future and current. Such a price sequence is called an unit impulse signal. Once the only non-zero input of 1 is entered into an infinite impulse response filter, this filter’s response or output will remain greater than 0 infinitely. An example of such a linear filter is the exponential moving average, EMA.

For a 3 period filter equivalent, or n=3, the EMA alpha = 2/(n+1) = 0.5

Suppose x_o=1 and x_i=0 for all other values of i. EMA will be 0 before x₀. The EMA at x₀ will be 0.5 x 0.50 x 1=0.5. For the next 10 days, x₁ to x₁₀ will be 0. However, EMA at x₁₀ will be 0.5 x 0.5¹⁰ x 1 = 0.000488. EMA after x₀ will never be 0. The past filter outputs will always feedback to the current output. The simple moving average on the other hand is a finite impulse response filter. If filter period is 3, the SMA at x₃ will be 0. Hence, the SMA will change abruptly when an older, but large observation is removed from the filter period, while the EMA phases out the older inputs gradually. Furthermore, the EMA is more responsive to the newer observations than the SMA. (See Chart below).

Conclusion

The key learning point of this article is to show that technical indicators are filters that extract features from a time series. It introduces different types of filters and illustrates how an unit impulse signal can be used to show the responsiveness and volatility of different technical studies.

However, a time series can also be represented as the sum of multiple oscillations with different frequencies and amplitudes. As the older values are never entirely excluded from the EMA, it is able to capture the effects of price patterns or cycle peaks and troughs that occur at higher frequencies or at intervals longer than the specified filter period. More will be elaborated on analyzing time series and filters as waves and signals in a separate article.

References

Gencay, Selcuk and Whitcher, 2002, An Introduction to Wavelets and Other Filtering Methods in Finance and Economics, Academic Press

An excellent book on wavelets analysis and filtering

Rowland, Todd and Weisstein, Eric W. "Linear Transformation." From MathWorld--A Wolfram Web Resource. http://mathworld.wolfram.com/LinearTransformation.html

A much more rigorous treatment of linear transformation

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Fisher Transform (VBA Code)

The code below provides a convenient sub routine to perform the Fisher transform. The FT_1 sub routine uses the high and low prices of your dataset as its inputs. You will also need to specify a number n representing the length of the normalising period in the transformation process. The sub routine produces 5 columns of outputs - the median price, normalised median price, smoothed normalised median price, Fisher transform and smoothed Fisher transform.

The exact steps of the Fisher transform and the meaning of the outputs can be obtained from here, where a more detailed explanation of the Fisher transform is provided. This article focuses on the VBA code for this indicator.

Sub Runthis()

Dim high As Range, low As Range

Dim output As Range, n As Long

Set high = Range("C2:C11955")

Set low = Range("D2:D11955")

Set output = Range("H2:H11955")

n = 5 'number of historical periods to look at

'needed for certain indicators

FT_1 high, low, output, n

End Sub

Sub FT_1(high As Range, low As Range, output As Range, n As Long)

output(0, 1).Value = "Median Price"

high0 = high(1, 1).Address(False, False)

low0 = low(1, 1).Address(False, False)

output(1, 1).Value = "=(" & high0 & "+" & low0 & ")/2"

output(1, 1).Copy output

output(0, 2).Value = "Normalized Price"

rangen = Range(output(1, 1), output(n, 1)).Address(False, False)

TP = output(n, 1).Address(False, False)

output(n, 2).Value = "=(((" & TP & "-min(" & rangen & "))/(max(" & rangen & ")-min(" & rangen & ")))-0.5)*2"

output(n, 2).Copy output.Offset(0, 1)

output(0, 3).Value = "Smoothed Normalized Price"

SNPtoday = output(n + 1, 2).Address(False, False)

SNPyesterday = output(n, 3).Address(False, False)

output(n + 1, 3).Value = "=max(-0.9999,min(0.9999,(0.5*" & SNPtoday & "+0.5*" & SNPyesterday & ")))"

output(n + 1, 3).Copy output.Offset(0, 2)

SNPyesterday = output(n, 2).Address(False, False)

output(n + 1, 3).Value = "=max(-0.9999,min(0.9999,(0.5*" & SNPtoday & "+0.5*" & SNPyesterday & ")))"

output(0, 4).Value = "Fisher Transform"

SNP = output(n + 1, 3).Address(False, False)

output(n + 1, 4).Value = "=0.5*ln((1+" & SNP & ")/(1-" & SNP & "))"

output(n + 1, 4).Copy output.Offset(0, 3)

output(0, 5).Value = "Smoothed Fisher Transform"

FTtoday = output(n + 2, 4).Address(False, False)

FTyesterday = output(n + 1, 5).Address(False, False)

output(n + 2, 5).Value = "=0.5*" & FTtoday & "+0.5*" & FTyesterday

output(n + 2, 5).Copy output.Offset(0, 4)

FTyesterday = output(n + 1, 4).Address(False, False)

output(n + 2, 5).Value = "=0.5*" & FTtoday & "+0.5*" & FTyesterday

Range(output(1, 2), output(n - 1, 5)).Clear

Range(output(n, 3), output(n, 5)).Clear

output(n + 1, 5).Clear

End Sub

The code provided above is definitely not efficient and will be updated over time. If you have any suggestions, do drop a comment below.

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Average Directional Movement Rating

Average Directional Movement Rating ADR

Comes with formula, calculation steps and VBA code

Introduction

The average directional movement rating ADR is based on the average directional index ADX. You might want to read about the average directional index before proceeding further. The ADR is calculated as follows:

ADR=(current ADX + ADX k periods ago)/2

As such, ADR can be seen as an approximate of the average ADX over n periods. The crossover of ADR and ADX can be seen as a signal confirming the existing of the trend. ADX is a measure of the strength of a trend, not direction. In an up or down market, the ADR and ADX values are always positive. Therefore, these indicators should be used with other trend indicators to infer the direction of the market.

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate ADR. Method B is faster and more flexible.

Method A

To calculate average directional movement rating, ADR, you will need the ADX custom function in your code, which in turn needs the following custom functions from the respective pages

DM function from directional movement

WWMA function from Welles Wilder's moving average

TR function from average true range

DMX function from directional movement index

In addition, you will need to have calculated ADX using Method A based on the steps provided in the page on average directional index. Below is the code for the custom function ADR.

'Insert the following into the AddUDF sub which you can obtain from any of the 'other pages listed above.

Application.MacroOptions macro:="ADR", _

Description:="Returns Average Directional Movement Rating" & Chr(10) & Chr(10) & _

"Select current ADX and ADX n periods ago", _

Category:="Technical Indicators"

'Insert below into the module

Public Function ADR(ADX, ADXn)

ADR = (ADX + ADXn) / 2

End Function

Once done, you can calculate ADR by entering "=ADR([current ADX], [ADX k periods ago])" into any cell.

Method B

Method B is much more convenient, with the intermediate steps calculated for you. To run Method B, you have to copy the Runthis sub of Method B from the page on Accumulation/Distribution line. You will run the ADR_1 sub from the Runthis sub.

However, you will still need the DM_1, WWMA_1, ATR_1, DMI_1, DMX_1, ADX_1 subs of Method B from the directional movement, Welles Wilder's moving average, average true range, directional movement indicator, directional movement index and average directional index pages respectively.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

ADR_1 high, low, close1, output, n, k

'Insert this as a new sub

Sub ADR_1(high As Range, low As Range, close1 As Range, output As Range, n As Long, k As Long)

ADX_1 high, low, close1, output, n

ADX0 = output(4 + k, 10).Address(False, False)

ADX1 = output(4, 10).Address(False, False)

output(0, 11).Value = "ADR"

output(4 + k, 11).Value = "=(" & ADX0 & "+" & ADX1 & ")/2"

output(4 + k, 11).Copy output.Offset(0, 10)

Range(output(1, 11), output(3 + k, 11)).Clear

End Sub

Other References

http://www.marketvolume.com/technicalanalysis/adxr.asp

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Weighted Moving Average

Weighted Moving Average WMA

Comes with formula, calculation steps and VBA code

The weighted moving average is calculated by giving each lagging price a smaller weight. The weight decreases at a constant rate from n to 0.

WMA = [n x Price_t + (n-1) x Price_t-1 + … + 2 x Price_t-n+2 + Price_t-n+1] / (n + (n-1) + …+ 2 + 1)

The divisor (n + (n-1) + …+ 2 + 1) can be calculated using the formula [n x (n+1)] / 2.

VBA Code

Two seperate methods are presented below.

Method A

To compute the weighted moving average of a series, enter into any cell "=WMA([n periods of your series])" after you have pasted the code below. The AddUDF sub routine adds your functions to a custom category called "Technical Indicators".

Sub AddUDF()

Application.MacroOptions macro:="WMA", _

Description:="Returns the Weighted Moving Average" & Chr(10) & Chr(10) & _

"Select n periods of prices", _

Category:="Technical Indicators"

End Sub

Public Function WMA(close_)

total1 = 0

n = WorksheetFunction.Count(close_)

divisor = (n * (n + 1)) / 2

For a = 1 To n

total1 = total1 + close_(a, 1) * a

Next a

WMA = total1 / divisor

End Function

Method B

In general, Method B is preferred over Method Afor large datasets. It is much more faster.

Sub Runthis()

Dim close1 As Range, output as Range, n As Long

Set close1 = Range("E2:E11955")

Set output = Range("H2:H11955")

n = 5 'number of historical periods to look at

'needed for certain indicators

WMA_1 close1, output, n

End Sub

Sub WMA_1(close1 As Range, output As Range, n As Long)

For a = 1 To n

output(a, 1).Value = a

Next a

numrange = Range(output(1, 1), output(n, 1)).Address

pricerange = Range(close1(1, 1), close1(n, 1)).Address(False, False)

output(n, 2).Value = "=sumproduct(" & numrange & "," & pricerange & ")/(" & n & "*(" & n & "+1)/2)"

output(n, 2).Copy output.Offset(0, 1)

Range(output(1, 2), output(n - 1, 2)).Clear

End Sub

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Directional Movement Index

Directional Movement Index DMX

Comes with formula, calculation steps and VBA code

Introduction

The directional movement index, DMX is derived from the positive and negative directional movement indicators, +DMI and -DMI. It is calculated as follows.

DMX = Absolute of [+DMI - (-DMI)] / [+DMI + (-DMI)]

The +DMI is the Welles Wilder's moving average of +DM over n days divided by the average true range for n days. -DMI is calculated in the same way. +DM refers to positive directional movement. Intuitively, it can be thought of as the change in the day's high. On the other hand, -DM or negative directional movement refers to the change in the day's low.

The directional movement index or DMX normalizes the absolute difference in DMI against the sum of +DMI and -DMI. It shows the strength in the current trend net of both upwards and downwards movement. If + DMI is similar in value to the -DMI, the value of DX will be small reflecting a weak trend. However, DX strictly tells nothing about the direction, only the strength of the trend because it is always an absolute value.

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate DMI. Method B is faster and more flexible.

Method A

To calculate +/-DMX, you need the following custom functions from the respective pages

DM function from directional movement

WWMA function from Welles Wilder's moving average

TR function from average true range

Calculate the following.

1. +DM and -DM, "=DM([current high], [current low], [previous high], [previous low])". Select the cell and the neigbouring cell right next to it, press F2 and Crtl+Shift+Enter. DM is an array function, returning both +DM and -DM
2. WWMA for +/- DM by entering in another cell "=WWMA([previous WWMA],[current +/-DM],[n])".
3. True range, "=TR([high],[low],[previous close])"
4. Average true range, "=WWMA([previous WWMA],[current TR],[n])"
5. +/-DMI=output from step 2 /output from step 4
6. DMX, "=DMX([+DMI],[-DMI])

'Paste this code into your ThisWorkBook code window in VBA. Right Click This WorkBook in Project Explorer and click View Code.

Private Sub Workbook_Open()

AddUDF

End Sub

'The rest belong to any module

Sub AddUDF()

'Tells Excel to includes these in list of functions, add descriptions to them and create a new category called Technical Indicators.

Application.MacroOptions macro:="DMX", _

Description:="Returns Directional Movement Index" & Chr(10) & Chr(10) & _

"Select positive and negative directional movement indicators", _

Category:="Technical Indicators"

End Sub

Public Function DMX(PosDMI, NegDMI)

DMX = Abs(PosDMI - NegDMI) / (PosDMI + NegDMI)

End Function

Method B

Method B offers the benefit of not having to the intermediate outputs, such as DMI yourself. It does everything for you. To run Method B, you have to copy the Runthis sub of Method B from the page on Accumulation/Distribution line. You will run the DMX_1 sub from the Runthis sub.

You will also need the DMI_1, DM_1, WWMA_1, ATR_1 subs of Method B from the directional movement indicator, directional movement, Welles Wilder's moving average and the average true range pages respectively.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

DMX_1 high, low, close1, output, n

'Insert this as a new sub

Sub DMX_1(high As Range, low As Range, close1 As Range, output As Range, n As Long)

DMI_1 high, low, close1, output, n

output(0, 9).Value = "DMX"

PosDMI = output(3, 7).Address(False, False)

NegDMI = output(3, 8).Address(False, False)

output(3, 9).Value = "=abs(" & PosDMI & "-" & NegDMI & ")/(" & PosDMI & "+" & NegDMI & ")"

output(3, 9).Copy output.Offset(0, 8)

Range(output(1, 9), output(2, 9)).Clear

End Sub

Other References

http://fxtrade.oanda.com/learn/graphs/indicators/adx.shtml

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Directional Movement Indicator

Directional Movement Indicator DMI

Comes with formula, calculation steps and VBA code

Introduction

The directional movement indicator DMI is the directional movement smoothed by the Welles Wilder's moving average normalized by the average true range. Like directional movement, the DMI is made of two studies - Positive DMI and Negative DMI or +DMI and -DMI respectively. Both +DMI and -DMI are behave like indices and are positive numbers. The +DMI and -DMI measures the strength of the up and the down trends respectively.

+DMI=WWMA(+DM,n) / ATR(n)

-DMI=WWMA(-DM,n) / ATR(n)

, where WWMA(DM,n) refers to a n period Welles Wilder's moving average of the DM and ATR(n) refers to a n day average true range.

Smoothing the DM and dividing the result by average range allows the DMI to be compared across different trading ranges and hence, across time. On the other hand, an upward sloping WWMA of the DMI alone may be inflated by a spike in trading ranges.

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate DMI. Method B is faster and more flexible.

Method A

To calculate +/-DMI, insert into a module the DM function from the page on directional movement, WWMA function from the page on Welles Wilder's moving average and TR function from the page on average true range.

1. Calculate +DM and -DM, by entering into any cell "=DM([current high], [current low], [previous high], [previous low])". Select the cell and the neigbouring cell right next to it, press F2 and Crtl+Shift+Enter.
2. Calculate WWMA for +/- DM by entering in another cell "=WWMA([previous WWMA],[current +/-DM],[n])".
3. Calculate true range, "=TR([high],[low],[previous close])".
4. Calculate average true range "=WWMA([previous WWMA],[current TR],[n])".
5. +/-DMI = ouput from step 2 / output from step 4

Method B

Method B is more convenient as it does not require you to remember the computation steps. To run Method B, you have to copy the Runthis sub of Method B from the page on Accumulation/Distribution line. You will run the DMI_1 sub from the Runthis sub.

You will need the DM_1, WWMA_1, ATR_1 subs of Method B from the directional movement, Welles Wilder's moving average and the average true range pages respectively.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

DMI_1 high, low, close1, output, n

'Insert this as a new sub

Sub DMI_1(high As Range, low As Range, close1 As Range, output As Range, n As Long)

DM_1 high, low, output

WWMA_1 output, output.Offset(0, 2), n

WWMA_1 output.Offset(0, 1), output.Offset(0, 3), n

Range(output(2, 3), output(2, 4)).Copy output(3, 3)

Range(output(2, 3), output(2, 4)).Clear

ATR_1 high, low, close1, output.Offset(0, 4), n

output0 = output(3, 3).Address(False, False)

output1 = output(3, 4).Address(False, False)

output2 = output(3, 6).Address(False, False)

output(0, 7).Value = "Positive DMI"

output(3, 7).Value = "=" & output0 & "/" & output2

output(3, 7).Copy output.Offset(0, 6)

output(0, 8).Value = "Negative DMI"

output(3, 8).Value = "=" & output1 & "/" & output2

output(3, 8).Copy output.Offset(0, 7)

Range(output(1, 7), output(2, 8)).Clear

End Sub

Other References

http://fxtrade.oanda.com/learn/graphs/indicators/adx.shtml

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Average True Range

Average True Range ATR

Comes with formula, calculation steps and VBA Code

The average true range is the Welles Wilder's moving average, WWMA of the true range, TR of a period. It is calculated as follows.

ATR=WWMA of TR

TR_t=max of (High_t,Close_t-1) - min of (Low_t,Close_t-1)

ATR=(TR_t+(n-1) x (WWMA of TR)_t-1) / n

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate APZ. Method B is faster and more flexible.

Method A

The average true range can be calculated by first computing the true range, before applying the WWMA function to it. The WWMA function can be obtained from here. The function for true range is as follows

'Paste this code into your ThisWorkBook code window in VBA. Right Click This WorkBook in Project Explorer and click View Code.

Private Sub Workbook_Open()

AddUDF

End Sub

'The rest belong to any module

Sub AddUDF()

'Tells Excel to includes these in list of functions, add descriptions to them and create a new category called Technical Indicators.

Application.MacroOptions macro:="TR", _

Description:="Returns True Range" & Chr(10) & Chr(10) & _

"Select current high, current low, last period's close.", _

Category:="Technical Indicators"

End Sub

Public Function TR(high, low, CloseYesterday)

TR = WorksheetFunction.Max(high, CloseYesterday) - WorksheetFunction.Min(low, CloseYesterday)

End Function

To compute true range, you just have to enter into any cell, "=TR([high],[low],[previous close])". The ATR can be computed by entering, "=WWMA([previous WWMA],[current TR],[n])".

Method B

Method B offers the benefit of not having to calculate true range yourself. It does everything for you. To run Method B, you have to copy the Runthis sub of Method B from the page on Accumulation/Distribution line and the WWMA_1 sub from the page on Welles Wilder's moving average. You will run the ATR sub from the Runthis sub.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

ATR high, low, close1, output, n

Sub ATR(high As Range, low As Range, close1 As Range, output As Range, n As Long)

'Get true range

output(0, 1).Value = "True Range"

high0 = high(2, 1).Address(False, False)

low0 = low(2, 1).Address(False, False)

close0 = close1(1, 1).Address(False, False)

output(2, 1).Value = "=max(" & high0 & "," & close0 & ")-min(" & low0 & "," & close0 & ")"

output(2, 1).Copy output

output(1, 1).Clear

'Get ATR

output0 = output(4, 1).Address(False, False)

output1 = output(3, 2).Address(False, False)

output(4, 2).Value = "=(" & output0 & "+(" & n & "-1)*" & output1 & ")/" & n

output(4, 2).Copy output.Offset(0, 1)

Range(output(1, 2), output(2, 2)).Clear

output0 = output(3, 1).Address(False, False)

output1 = output(2, 1).Address(False, False)

output(3, 2).Value = "=(" & output0 & "+(" & n & "-1)*" & output1 & ")/" & n

End Sub

Other References

http://fxtrade.oanda.com/learn/graphs/indicators/adx.shtml#atr

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Average Directional Index

Average Directional Index ADX

Comes with formula, calculation steps and VBA code

Introduction

The average directional index, ADX is a relatively complex indicator involving a total of five other technical studies. They are Welles Wilder's moving average, average true range, directional movement, directional movement indicator and directional movement index. The ADX is a measure of trend strength, but not a measure of direction. You might want to visit the pages above before reading further.

The calculation of the ADX invovles four major steps.

1. Calculate the directional movement of each period based on the period high and low.
2. Smooth the directional movement using the Welles Wilder's moving average and divide the result by the average true range for the last n days to obtain the directional movement indicator.
3. Compute the directional movement index from the directional movement indicator to determine the strength of prevailing trend.
4. Smooth the directional movement index using the Welles Wilder's moving average to obtain the average directional index.

Thus, the ADX is a smoothed view of trend strength. The ADX does not measure market direction because the directional movement index is always a positive value. Its formula is as follows:

ADX=WWMA of DMX over k periods

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate DMI. Method B is faster and more flexible.

Method A

To calculate average directional index, ADX, you need the following custom functions from the respective pages

DM function from directional movement

WWMA function from Welles Wilder's moving average

TR function from average true range

DMX function from directional movement index

Calculate the following.

1. +DM and -DM, "=DM([current high], [current low], [previous high], [previous low])". Select the cell and the neigbouring cell right next to it, press F2 and Crtl+Shift+Enter. DM is an array function, returning both +DM and -DM
2. WWMA for +/- DM by entering in another cell "=WWMA([previous WWMA],[current +/-DM],[n])".
3. True range, "=TR([high],[low],[previous close])"
4. Average true range, "=WWMA([previous WWMA],[current TR],[n])"
5. +/-DMI=output from step 2 /output from step 4
6. DMX, "=DMX([+DMI],[-DMI])
7. ADX, "=WWMA([previous ADX], [current DMX], [n])"

As can be seen, the calculation of the ADX is actually a WWMA function of DMX. I try to avoid writing custom functions that only run another custom function because it is repetitive. The function of ADX if I were to write one, will look like this

Public Function ADX(ADXPrevious, DMX, n)

ADX=WWMA(ADXPrevious,DMX,n)

End Function

Sub AddUDF()

Application.MacroOptions macro:="ADX", _

Description:="Returns Average Directional Movement Index" & Chr(10) & Chr(10) & _

"Select previous period's ADX, current DMX and n", _

Category:="Technical Indicators"

End Sub

The ADX function above merely passes the same inputs to the WWMA function.

Method B

Method B is much more convenient because it calculates the intermediate outputs for you. To run Method B, you have to copy the Runthis sub of Method B from the page on Accumulation/Distribution line. You will run the ADX_1 sub from the Runthis sub.

You will need the DM_1, WWMA_1, ATR_1, DMI_1, DMX_1 subs of Method B from the directional movement, Welles Wilder's moving average, the average true range, directional movement indicator, directional movement index pages respectively.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

ADX_1 high, low, close1, output, n

'Insert this as a new sub

Sub ADX_1(high As Range, low As Range, close1 As Range, output As Range, n As Long)

DMX_1 high, low, close1, output, n

WWMA_1 output.Offset(0, 8), output.Offset(0, 9), n

output(0, 10).Value = "ADX"

output(2, 10).Copy output(4, 10)

Range(output(2, 10), output(3, 10)).Clear

End Sub

ADX_1 only has seven lines because much of the work is done by the DMX_1 and WWMA_1 subs.

The ADX is used in the calculation of the average directional movement rating ADR.

Other References

http://fxtrade.oanda.com/learn/graphs/indicators/adx.shtml#atr

http://www.marketvolume.com/technicalanalysis/adxr.asp

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Adaptive Price Zone

Adaptive Price Zone APZ

Read on for formula, calculation steps and VBA code. For naive backtest results and findings, visit here

The Adaptive Price Zone APZ indicator is from the Technical Analysis of Stocks & Commodities Magazine, September 2006 article "Trading With An Adpative Price Zone" by Lee Leibfarth. It has an upper and lower boundary and prices are expected to reverse when it hits either boundar.

Upper APZ boundary = EMA of (EMA of Price for n periods) for n periods + APZRange

Lower APZ boundary = EMA of (EMA of Price for n periods) for n periods – APZRange

where APZRange = EMA of (High – Low) for n periods x band factor.

Both band factor and n are the user’s choice. Band factor adjusts the width of the adaptive price zone. EMA refers to the exponential moving average. As can be seen, the adaptive price zone is based on the exponential moving average of an exponential moving average.

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate APZ. Method B is faster and more flexible.

Method A

Like the Chaikin oscillator, the adaptive price zone can be computed using a series of exponential moving average custom functions. Nonetheless, a custom function to compute the APZ is provided below. Note that it is an array function and outputs the center line, upper and lower bands of the adaptive price zone. To display the entire set of outputs, enter the APZ function into the one cell and select it along with the 2 cells horizontally next to it, press F2 and Enter.

Your inputs are both the current and last price and range EMAs, as well as the band factor and average period n. Your VBA code must include the EMA function for the APZ function to work.

'Paste this code into your ThisWorkBook code window in VBA. Right Click This WorkBook in Project Explorer and click View Code.

Private Sub Workbook_Open()

AddUDF

End Sub

'The rest belong to any module

Sub AddUDF()

'Tells Excel to includes these in list of functions, add descriptions to them and create a new category called Technical Indicators.

Application.MacroOptions macro:="APZ", _

Description:="Returns the center line, upper band and lower band the adaptive price zone" & Chr(10) & Chr(10) & _

"Input current EMA of price, last period's EMA of price, current EMA of (High-Low), last period's EMA of (High-Low), band_factor and n the averaging period.", _

Category:="Technical Indicators"

End Sub

Public Function APZ(CurrentEMA_Price, LastEMA_Price, CurrentEMA_Range, LastEMA_Range, band_factor, n)

Dim result(0, 1 To 3)

result(0, 1) = EMA(LastEMA_Price, CurrentEMA_Price, n)

bandwidth = EMA(LastEMA_Range, CurrentEMA_Price, n) * band_factor

result(0, 2) = result(0, 1) + bandwith

result(0, 3) = result(0, 1) - bandwidth

APZ = result

End Function

Once you are done with the above, you can calculate the adaptive price zone by entering into any cell, "=APZ(Current EMA of Price, Last EMA of Price, Current EMA of Range, Last EMA of Range, band factor, n)". As such, you need to calculate the respective EMAs before hand. Select the cell and its 2 neighbours, press F2 and Enter to display the entire set of outputs.

Method B

Method B offers the benefit of not having you calculation any exponential averages in advance. It does everything for you. To run Method B, you have to copy the Runthis sub and the EMA sub of Method B from the page on Accumulation/Distribution line and exponential moving average into your module. You will run the APZ sub from the Runthis sub.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

Dim band_factor As Double

band_factor=2

'Range_1 high, low, output

APZ_1 high, low, close1, output, band_factor, n

Sub Range_1(high, low, output)

output(0, 1).Value = "Range"

high0 = high(1, 1).Address(False, False)

low0 = low(1, 1).Address(False, False)

output(1, 1).Value = "=" & high0 & "-" & low0

output(1, 1).Copy output

End Sub

Sub APZ_1(high As Range, low As Range, close1 As Range, output As Range, band_factor As Double, n As Long)

'Use the Range and EMA functions to calculate components of the APZ

EMA close1, output, n

output(0, 1).Value = "EMA_Price"

Range_1 high, low, output.Offset(0, 1)

EMA output.Offset(0, 1), output.Offset(0, 2), n

output(0, 3).Value = "EMA_Range"

output0 = output(1, 3).Address(False, False)

output(0, 4).Value = "APZ_Width"

output(1, 4).Value = "=" & output0 & "*" & band_factor

output(1, 4).Copy output.Offset(0, 3)

output(0, 5).Value = "APZ_Upper"

output0 = output(4, 6).Address(False, False)

output1 = output(4, 4).Address(False, False)

output(4, 5).Value = "=" & output0 & "+" & output1

output(0, 7).Value = "APZ_Lower"

output(4, 7).Value = "=" & output0 & "-" & output1

output(4, 5).Copy output.Offset(0, 4)

output(4, 7).Copy output.Offset(0, 6)

EMA output, output.Offset(0, 5), n

output(2, 6).Copy output(3, 6)

output(0, 6).Value = "APZ_Center"

Range(output(1, 5), output(2, 7)).Clear

End Sub

Other References

http://www.traders.com/documentation/FEEDbk_docs/2006/09/TradersTips/TradersTips.html#tradestation

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Chaikin Oscillator

Chaikin Oscillator COSC

Comes with formula, calculation steps and VBA code

Introduction

The Chaikin oscillator is very similar to the Chaikin money flow, except that it is actually the difference between two moving averages of the Accumulation/Distribution line or ADL. Some articles uses a simple moving averge, while some propose that an exponential moving average be used instead. I choose the exponential moving average. The Chaikin oscillator is calculated as such:

Chaikin Oscillator=n period EMA of the ADL - k period EMA of the ADL

An n period exponential moving average has a decay factor of 2/(n+1). An article on the exponential moving average, its formula and VBA code can be found here, while the Accumulation/Distribution line and its VBA code is covered in an article here.

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate CMF. Method B is faster and more flexible.

Method A

Insert the VBA code for Method A of the Accumulation/Distribution line.

Insert the VBA code for Method A of the exponential moving average.

Calculate ADL on another column in your spreadsheet either using your own formula or the ADL function provided by me. If you are using my custom functions, you just need to enter into any cell, "=ADL([Last period ADL],[Current High], [Current Low], [Current Close],[Current Volume])" to compute current ADL.

Compute a fast exponential moving average for the ADL. Using my custom function, you enter into any cell, "=EMA([Last Period EMA],[Current ADL],[n])". Enter last period ADL as last period EMA when you are computing the first EMA of your dataset. [n] is your averaging period. Repeat the above with a larger average period. The Chaikin oscillator is the difference between the slow moving average and the fast moving average

If you must have a custom function to work with, you can follow the instructions below. You will still need to insert the VBA code for the ADL and EMA functions. The COSC function is an array function that will output the Chaikin Oscillator, ADL, Fast EMA, Slow EMA.

'Paste this code into your ThisWorkBook code window in VBA. Right Click This WorkBook in Project Explorer and click View Code.

Private Sub Workbook_Open()

AddUDF

End Sub

'The rest belong to any module

Sub AddUDF()

'Tells Excel to includes these in list of functions, add descriptions to them and create a new category called Technical Indicators.

Application.MacroOptions macro:="COSC", _

Description:="Returns Chaikin Oscillaor, current ADL, Fast EMA and Slow EMA." & Chr(10) & Chr(10) & _

"Input current high, low, close, volume, n, k, last period's ADL,fast EMA and slow EMA. n and k are the averaging periods for the fast and slow moving averages respectively.", _

Category:="Technical Indicators"

End Sub

Public Function COSC(high, low, close_, volume, n, k, ADLYesterday, FastEMAYesterday, SLowEMAYesterday)

Dim result(0, 1 To 4)

result(0, 2) = ADLYesterday + CLV(high, low, close_) * volume 'ADL

result(0, 3) = EMA(FastEMAYesterday, result(0, 2), n) 'EMAFast

result(0, 4) = EMA(SLowEMAYesterday, result(0, 2), k) 'EMASlow

result(0, 1) = result(0, 3) - result(0, 4) 'Oscillator

COSC = result

End Function

Once you are done with the above, you can compute Chaikin oscillator by entering into any cell, "=COSC([Current High], [Current Low], [Current Close],[Current Volume],...)". Select the cell and the three cells horizontally next to it, press F2 and Enter to display the full set of outputs.

Method B

To run Method B, you have to copy the Runthis sub and the CLV, ADL and EMA sub of Method B from the page on Accumulation/Distribution line and exponential moving average into your module. You will runthe COSC sub from the Runthis sub.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

COSC_1 high, low, close1, volume, output, n, k

Sub COSC_1(high As Range, low As Range, close1 As Range, volume As Range, output As Range, n As Long, k As Long)

output(0, 6).Value = "Chaikin Oscillator"

'Calculate the ADL and EMA needed

ADL_1 high, low, close1, volume, output

EMA_1 output.Offset(0, 2), output.Offset(0, 3), n

EMA_1 output.Offset(0, 2), output.Offset(0, 4), k

'Calculate Chaikin oscillator

output0 = output(2, 4).Address(False, False)

output1 = output(2, 5).Address(False, False)

output(2, 6).Value = "=" & output0 & "-" & output1

output(2, 6).Copy output.Offset(0, 5)

output(1, 6).Clear

End Sub

Other References

http://www.gannalyst.com/Gannalyst_Professional/Gannalyst_Indicators_ChaikinOscillator.shtml

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Chaikin Money Flow

Chaikin Money Flow CMF

Comes with formula, calculation steps and VBA code

Introduction

The Chaikin money flow, CMF is calculated as

CMF = Sum of (CLV x Volume) for last n periods / Sum of Volume for last n periods

, where n is specified by the user. (CLV x Volume) is a proxy of money flow in and out of a stock. The numerator, sum of (CLV x Volume) for the last n periods is actually very similar to the Accumulation/Distribution Line, ADL. While the ADL is the cumulative sum of (CLV x Volume) over your entire dataset, CMF only looks at the last n periods of money flow.

More information about ADL and its VBA code can be found here.

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate CMF. Method B is faster and more flexible.

Method A

'Paste this code into your ThisWorkBook code window in VBA. Right Click This WorkBook in Project Explorer and click View Code.

Private Sub Workbook_Open()

AddUDF

End Sub

'The rest belong to any module

Sub AddUDF()

'Tells Excel to includes these in list of functions, add descriptions to them and create a new category called Technical Indicators.

Application.MacroOptions macro:="CMF", _ Description:="Returns the Chaikin Money Flow." & Chr(10) & Chr(10) & _

"Select n periods of high, low, close and volume", _ Category:="Technical Indicators"

End Sub

Public Function CMF(high, low, close_, volume)

no0 = volume.Count numerator = 0

For a = 1 To no0

numerator = numerator + ((close_(a, 1) - low(a, 1)) - (high(a, 1) - close_(a, 1))) / (high(a, 1) - low(a, 1)) * volume(a, 1)

Next a

CMF = numerator / WorksheetFunction.Sum(volume)

End Function

Once you are done with the above, you can compute Chaikin money flow by entering into any cell, "=CMF([Current High], [Current Low], [Current Close],[Current Volume])".

Method B

To run Method B, you have to copy the Runthis sub and the CLV sub of Method B from the page on ADL into your module.

'Copy the following line into the Runthis sub

'Just before the line End Sub

'Disable all other macros that Runthis will call e.g. CLV, ADL, by

'marking them out as comments with single quotes

CMF_1 high, low, close1, volume, output, n

Sub CMF_1(high As Range, low As Range, close1 As Range, volume As Range, output As Range, n As Long)

output(0, 3).Value = "CMF"

'Calculate CLV first

CLV_1 high, low, close1, output

CLV0 = Range(output(1, 2), output(n, 2)).Address(False, False)

vol0 = Range(volume(1, 1), volume(n, 1)).Address(False, False)

'Calculate CMF

output(n, 3).Value = "=SUMPRODUCT(" & CLV0 & "," & vol0 & ")/SUM(" & vol0 & ")"

output(n, 3).Copy output.Offset(0, 2)

Range(output(1, 3), output(n - 1, 3)).Clear

End Sub

Other References

http://www.gannalyst.com/Gannalyst_Professional/Gannalyst_Indicators_ChaikinMoneyflow.shtml

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)

Moving Average Convergence/Divergence

Moving Average Convergence/Divergence MACD

Comes with formula, calculation steps and VBA Code

The MACD is based on the difference of two exponential moving averages. Convergence signifies the end of a trend, while divergence means that a trend is forming. To compute the MACD, users need to specify

a) Fast, the number of periods the slow moving average is based on,

b) Slow, the number of periods the slow moving average is based on and

c) n, the number of periods MACD is smoothed against.

Step 1: Compute the fast and slow exponential moving averages, EMAs.

Fast EMA_t = [(2 / (1+Fast)] x Price + [1 – (2/(1+Slow))] x Yesterday’s Fast EMA_t-1

Slow EMA_t = [(2 / (1+Fast)] x Price + [1 – (2/(1+Slow))] x Slow EMA_t-1

Step 2: Calculate MACD, Average MACD, Difference

MACD = Fast EMA – Slow EMA

Average MACD = (2 / (1 + n)) x MACD + [1 – (2 / (1+n))] x Yesterday’s MACD

Difference = MACD – Average MACD

The picture below shows you how the MACD, Average MACD and Difference are plotted on a price chart.

VBA Code

The MACD can be calculated in Excel using user defined functions as in Method A or via sub routines in Method B.

Method A

The MACD custom function provided below is an array function that returns 3 values - the MACD, average MACD and the difference between the two outputs. The inputs are the current period's fast and slow exponential moving averages, the prior period MACD and n the smoothing period of the average MACD. To calculate MACD for the current period, input in any cell, "=MACD([fast EMA], [slow EMA], [previous MACD], [n])". Select the cell and the 2 cells next to it and press F2 followed by Crtl+Shift+Enter.

'This sub adds the MACD function to a list of functions called Technical Indicators

Sub AddUDF()

Application.MacroOptions macro:="MACD", _

Description:="Returns Moving Average Convergence/Divergence, Average MACD and Difference" & Chr(10) & Chr(10) & _

"Select current fast EMA, slow EMA, previous MACD and n, the smoothing period of the average MACD", _

Category:="Technical Indicators"

End Sub

Public Function MACD(fastEMA, slowEMA, MACDYesterday, n)

Dim result(0, 1 To 3)

result(0, 1) = fastEMA - slowEMA

alpha = 2 / (1 + n)

result(0, 2) = alpha * result(0, 1) + (1 - alpha) * MACDYesterday

result(0, 3) = result(0, 1) - result(0, 2)

MACD = result

End Function

Method B

Method B does not require you to compute the exponential moving average in advance. You need only input historical prices, where you want the output to be located, n the smoothing period of the fast EMA, k the smoothing period of the slow EMA and j the smoothing period of the MACD. I typically write a sub called MACD_1 and call this sub from a major sub with all the variables.

Sub Runthis()

Dim close1 As Range, output As Range, n As Long

Dim k As Long, j As Long

Set close1 = Range("E2:E11955")

Set output = Range("H2:H11955")

n = 5 'number of historical periods to look at

'needed for certain indicators

k = 10 'number of historical periods to look at

'needed for certain indicators as slow average

j = 5 'number of historical periods to look at

'to smooth the MACD

MACD_1 close1, output, n, k, j

End Sub

Sub MACD_1(close1 As Range, output As Range, n As Long, k As Long, j As Long)

EMA_1 close1, output, n

output(0, 1).Value = "FastEMA"

EMA_1 close1, output.Offset(0, 1), k

output(0, 2).Value = "SlowEMA"

output(0, 3).Value = "MACD"

EMAF = output(2, 1).Address(False, False)

EMAS = output(2, 2).Address(False, False)

output(2, 3).Value = "=" & EMAF & "-" & EMAS

output(2, 3).Copy output.Offset(0, 2)

output(0, 4).Value = "Avg MACD"

DIFFL = output(2, 3).Address(False, False)

DIFFN = output(3, 3).Address(False, False)

DIFFC = output(4, 3).Address(False, False)

MACDL = output(3, 4).Address(False, False)

output(4, 4).Value = "=(2/(1+" & j & "))*" & DIFFC & "+(1-(2/(1+" & j & ")))*" & MACDL

output(4, 4).Copy output.Offset(0, 3)

output(3, 4).Value = "=(2/(1+" & j & "))*" & DIFFN & "+(1-(2/(1+" & j & ")))*" & DIFFL

output(0, 5).Value = "Difference"

MACDA = output(3, 4).Address(False, False)

MACDN = output(3, 3).Address(False, False)

output(3, 5).Value = "=" & MACDN & "-" & MACDA

output(3, 5).Copy output.Offset(0, 4)

Range(output(1, 3), output(1, 5)).Clear

Range(output(2, 4), output(2, 5)).Clear

End Sub

The code above is not optimal. There are repetitive sections that can be removed. Nonetheless, it serves as an excellent point for further refinement.

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)