Get a VBA-powered spreadsheet that plots Aroon from historical prices downloaded on-the-fly from Yahoo Finance.
This is the second in a two-part article about the Aroon indicator.
The first part introduced Aroon and explored how it’s used to make better trading decisions.
This part introduces a Excel spreadsheet that plots the indicator via data supplied via connectivity to Yahoo Finance. VBA automates the entire process.
You can download the complete spreadsheet from the link at the bottom of this post. However, read the rest of this article to learn how the critical parts of the VBA are structured.
Briefly, Aroon consists of three signals
- Aroon up is equal to 100 x (period – number of days since period high) / period
- Aroon down is equal to 100 x (period – number of days since period low) / period
- Aroon oscillator is equal to Aroon up minus Aroon down.
The period is usually 25 days, but can be made longer or shorter to quantify the strength of trends with different characteristic time periods.
The VBA contains two major subroutines.
- The first downloads historical market data from Yahoo Finance.
- The second calculates Aroon, and creates two plots – one for the oscillator (and the close price), and another for the up/down signals
The most important part of the Aroon subroutine are the following lines
dataSheet.Range("h" & 1 + nperiod & ":h" & nRows).FormulaR1C1 = _ "=100-100*(1" & "-MATCH(MAX(R[-" & nPeriod - 1 & "]C[-1]:RC[-1]),R[-" & nPeriod - 1 & "]C[-1]:RC[-1],0)/" & nPeriod & ")" dataSheet.Range("i" & 1 + nperiod & ":i" & nRows).FormulaR1C1 = _ "=100-100*(1" & "-MATCH(MIN(R[-" & nPeriod - 1 & "]C[-2]:RC[-2]),R[-" & nPeriod - 1 & "]C[-2]:RC[-2],0)/" & nPeriod & ")" dataSheet.Range("j" & 1 + nPeriod & ":j" & nRows).FormulaR1C1 = _ "=RC[-2]-RC[-1]" |
The adjusted close prices are in column h (in the sheet dataSheet), nRows is the number of rows, and nPeriod is the number of rows of historical data.The first line calculates Aroon up, the second Aroon down, and the third the oscillator.
The VBA use the R1C1 cell referencing method to programmatically create the formulas. For the up and down code,
- Max() and Min() calculate the maximum or minimum price in the trailing time period
- Match() calculates the position of the maximum or minimum price in the trailing time period
This, for example, is the structure of the nested Match/Max command for Aroon up (where R[-” & nPeriod – 1 & “]C[-1]:RC[-1] is the Excel range of close prices in the trailing time period)
This is the entire VBA for the Aroon function, including the code that creates the plots.
Sub Aroon() Set parameterSheet = Sheets("Parameters") Set dataSheet = Sheets("Data") nRows = dataSheet.UsedRange.Rows.Count - 1 nPeriod = parameterSheet.Range("nPeriod") dataSheet.Range("h1") = "Aroon Up" dataSheet.Range("i1") = "Aroon Down" dataSheet.Range("j1") = "Aroon Oscillator" dataSheet.Range("h" & 1 + nPeriod & ":h" & nRows).FormulaR1C1 = _ "=100-100*(1" & "-MATCH(MAX(R[-" & nPeriod - 1 & "]C[-1]:RC[-1]),R[-" & nPeriod - 1 & "]C[-1]:RC[-1],0)/" & nPeriod & ")" dataSheet.Range("i" & 1 + nPeriod & ":i" & nRows).FormulaR1C1 = _ "=100-100*(1" & "-MATCH(MIN(R[-" & nPeriod - 1 & "]C[-2]:RC[-2]),R[-" & nPeriod - 1 & "]C[-2]:RC[-2],0)/" & nPeriod & ")" dataSheet.Range("j" & 1 + nPeriod & ":j" & nRows).FormulaR1C1 = _ "=RC[-2]-RC[-1]" Set parameterSheet = parameterSheet Set dataSheet = dataSheet Dim ch As ChartObject For Each ch In parameterSheet.ChartObjects ch.Delete Next Dim AroonChart As ChartObject Set AroonChart = parameterSheet.ChartObjects.Add(Left:=Range("a11").Left, Width:=500, Top:=Range("a11").Top, Height:=300) With AroonChart.Chart .Parent.Name = "AroonOscillator" With .SeriesCollection.NewSeries .ChartType = xlLine .AxisGroup = xlPrimary .XValues = dataSheet.Range("a2:a" & nRows) .Values = dataSheet.Range("g2:g" & nRows) .Name = "Adjusted Close" .Border.ColorIndex = 1 .Format.Line.Weight = 2 End With With .SeriesCollection.NewSeries .ChartType = xlLine .XValues = dataSheet.Range("a2:a" & nRows) .Values = dataSheet.Range("j2:j" & nRows) .Format.Line.Weight = 1 .Name = "Aroon Oscillator" .AxisGroup = xlSecondary End With .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Adjusted Close" .Axes(xlValue, xlSecondary).HasTitle = True .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Aroon Oscillator" .Axes(xlValue, xlSecondary).MaximumScale = 100 .Axes(xlValue, xlSecondary).MinimumScale = -100 .Axes(xlValue, xlPrimary).MaximumScale = WorksheetFunction.Max(dataSheet.Range("g2:g" & nRows)) .Axes(xlValue, xlPrimary).MinimumScale = Int(WorksheetFunction.Min(dataSheet.Range("g2:g" & nRows))) .Legend.Position = xlLegendPositionBottom .SetElement (msoElementChartTitleAboveChart) .ChartTitle.Text = "Aroon Oscillator for " & parameterSheet.Range("ticker") .ChartTitle.Font.Size = 14 End With Dim AroonUpDownChart As ChartObject Set AroonUpDownChart = parameterSheet.ChartObjects.Add(Left:=Range("a32").Left, Width:=500, Top:=Range("a32").Top, Height:=300) With AroonUpDownChart.Chart .Parent.Name = "Aroon Up & Dow" With .SeriesCollection.NewSeries .ChartType = xlLine .AxisGroup = xlPrimary .XValues = dataSheet.Range("a2:a" & nRows) .Values = dataSheet.Range("h2:h" & nRows) .Name = "Up" .Border.ColorIndex = 1 .Format.Line.Weight = 2 .Border.Color = RGB(119, 158, 203) End With With .SeriesCollection.NewSeries .ChartType = xlLine .XValues = dataSheet.Range("a2:a" & nRows) .Values = dataSheet.Range("i2:i" & nRows) .Format.Line.Weight = 2 .Name = "Down" .AxisGroup = xlPrimary .Border.Color = RGB(222, 165, 164) End With .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Aroon Up & Down Trend" .Axes(xlValue, xlPrimary).MaximumScale = 100 .Axes(xlValue, xlPrimary).MinimumScale = 0 .Legend.Position = xlLegendPositionBottom .SetElement (msoElementChartTitleAboveChart) .ChartTitle.Text = "Aroon Up & Down for " & parameterSheet.Range("ticker") .ChartTitle.Font.Size = 14 End With End Sub |
The spreadsheet is simple to use, and contains the code above. You just enter a ticker, two dates and click a button. Feel free to learn, modify and expand the VBA.
Download Excel Spreadsheet to Calculate and Plot Aroon with Connectivity to Yahoo Finance