Calculate Aroon with Excel and VBA

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.An Excel spreadsheet that automatically calculates and plots the Aroon Oscillator from historic market data downloaded from Yahoo Finance

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)

aroon command

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


Leave a Reply

Your email address will not be published. Required fields are marked *

What is 10 + 13 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)