Ho, Ho, Ho: Excel VBA For Global Equities Momentum

Just in time for Santa! Based again on a foundation by InvestExcel, Denis Bergemann collaborated with me on another Excel VBA project covering Gary Antonacci's popular Global Equities Momentum (GEM).

The VBA driven Excel spreadsheet follows the official rules for GEM (see here) and allows you to select your preferred US and International stocks fund. This applies also for the out-of-market bond fund and for the proxy fund for observing the risk free rate. The lookback parameter for both relative and absolute momentum is user adjustable.

[Update] In the latest edition of the spreadsheet [v3], the widely used 60/40 benchmark is depicted as a reference point. The 60/40 portfolio holds 60% equities and 40% bonds with monthly rebalancing. In the spreadsheet the 60/40 mix is composed of the US stocks fund and the out-of-market bond fund.

Results for both the GEM and 60/40 portfolios as well as the separate components are presented in tabular and graphical format.

The [updated] work flow is pretty much identical to the one for the PAA / GPM VBA (see here):

• Currently, the sheet only works with Excel for Windows
• Run the backtest after Yahoo Finance has published the closing data for the month
• Enter the [two] required stock funds in the dedicated column of the sheet
• Repeat for the out-of-market bond fund and the proxy fund resembling the risk-free rate
• Set start and end dates
  (NB! Observe an initialization period for the fund with the shortest history equal to the chosen lookback range)
• Click the button [Download Data] and wait until downloading has finished (graphs and tables will disappear)
• Finally click the button [Allocation] and wait for the VBA to crunch the numbers (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.

Please observe the required number of [four] assets (no more, no less!) as indicated in the spreadsheet.

Running the spreadsheet at the end of the month, after Yahoo Finance has published the closing data for the month, will give the allocation for the upcoming month. End of month data is usually available a couple of hours after the NYSE market session has ended on the last trading day of the month. Since GEM deploys an all-in approach, the allocation percentage is 100% of dedicated capital.

Merry Xmas!

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