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.

Value Investing By Proxy

By many Benjamin Graham is considered to be the father of "value investing" and with his book "The Intelligent Investor", Mr. Graham brought value investing to the individual investor.

Mr. Graham advised the investor to always hold bonds in his investment portfolio:

"We recommend that the investor divide his holdings between high-grade bonds and leading common stocks; that the proportion held in bonds be never less than 25% or more than 75% with the converse being necessarily true for the common-stock component."

Combining Mr. Graham's recommendation with the concept of paired switching previously discussed, leads to the following long only application:

• Intermittently rebalance a basket of VTI (stocks) and TLT (bonds) and allocate 75% of capital to the best performing EFT and the remaining 25% to the other ETF.
• The rebalancing frequency may be set for example to quarterly or monthly.
• Performance is measured using total return over the prior quarter or prior three months.

Building such a "Value Investing By Proxy" model in Excel is fairly easy using monthly adjusted closes available from Yahoo! Finance (VTI, TLT).