Flexing VBA For Quants (And Everyone Else)

Update: 

Due to a change in the placement of OHLC price data in Tiingo's feed, version 4.0 of the stand alone Excel spreadsheet should no longer be used! In the meantime JH has fixed the issue, which is available as beta version 5.0. Thanks JH, great job!

Would it not be great to have the models for Protective Asset Allocation (PAA) and Global Protective Momentum (GPM) in Excel, so you can run your own backtests without AmiBroker? And not being limited to a pre-defined universe? Actually, now you can.

Based on a foundation by InvestExel, Denis Bergemann from Germany collaborated with me in developing an Excel spreadsheet that allows you to select your preferred risk-on and risk-off assets, set backtest parameters to your liking and review results by their statistics as well as in graphical format.

The work flow goes like this:

• Run the backtest after Yahoo has published the closing data for the month
• Enter the risk-on and risk-off assets in the two dedicated columns of the sheet
• Set start and end dates
  (NB! Observe a 1-year initialization period for the ETF with the shortest history)
• Keep frequency at m (=monthly quotes)
• Adjust protection level* (default = 2: high protection)
• Select number of top assets
• Click the button [Download Data] and wait until downloading has finished (graphs and tables will disappear)
• Finally click the button [Calculate Systems] and wait for the VBA magic to finalize (graphs and tables are reprocessed).

The VBA-code embedded in the Excel sheet takes care of downloading the data as well as all the necessary calculations and permutations.

The sheet allows for adjusting the protection level or the top selection without a new download, but changing the backtest range or altering the asset lists requires to start with a fresh data download.

Please note the number of risk-on or risk-off assets is not restricted to 12 and 2, just select the ETFs and/or mutual funds you prefer.

The sheet also shows the allocation percentages for the upcoming month after Yahoo has published the monthly closing data, usually available a couple of hours after the NYSE market session has ended on the last trading day of the month.

End notes

• The sheet only works with Windows.
• The protection level is simplified to ( p * ∑ [ri <= 0] ) / N, so p = 0 turns the protection logic off. With p = 2, the protection level equals high protection as used in the presentation of PAA and GPM.

The Excel sheet for PAA / GPM is available upon request. Interested parties are encouraged to support this blog with a donation.