Experimental – Offline Excel Portfolio Builder

[Alex @ Logical Invest]

This topic is to maintain a log of the development activities for the “Offline Excel Portfolio Builder”. Interested people are invited to join in to debug and further improve this tool.

Note: Currently, this is highly experimental, still buggy, and focused only on functionality, not design. The tool is written in Excel 2013, some cross-compatibility features are build in, but still might behave strange in other versions.

I will build a “ToDo” List out of the comments, and post updates here in the forum topic. Feel free to add your own improvements, VBA code, etc and mail me at [email protected] to post.

———————————————-

Last Version:
Offline Excel Portfolio Builder V0.1

Features:
– Updates now dynamically from site, the input file updates daily at 6pm EST (see button on ‘Instructions’ tab)
– Uses new adaptive versions where available, so better for fwd looking portfolio setup
– All equity lines are daily, and now can also calculate daily MaxDD and duration
– Added the UIS-SPXL-TMF version which I like, and have reserved 7 slots for new strategies or meta-strategies
– Added 17 ‘pre-configured’ solver optimized portfolios in drop-down menu… we can expand by whatever we want..
o These are a good start for new people, or less experienced excel freaks..
o Feel free to add or suggest other portfolios, they go into tab ‘PortfolioTemplates’
– Added Solver optimization using either pre-configured ‘scenarios’ or custom input
o This is very powerful for experienced guys, examples
– Highest Sharpe with <15% vola or <15% drawdown..
- Leveraged portfolio with lowest drawdown
- Including / Excluding certain strategies from optimization
- Min / Max weights by strategies
- Will add some more VBA stuff later, but first wanted to migrate to google sheet for site

[hawaiianwaverider]

Hi Alex, being the first non-LI person to look at the spreadsheet, great job! This brings an exciting tool so we can tailor portfolios’s to what matters to us.

I would like the ability to have a portfolio that doesn’t achieve gains from long U.S treasuries as have some of the portfolios here. So, I guess non-TLT as one of the rotational (screened) positions.

Keep up the great work. I just got the email showing the future strategies and you guys are doing great on many fronts.

[William Wong]

That’s a great work! I am particularly interested in this offline tool of portfolio optimizer. Since I am using the 2003 excel version, I am only authorized to view your file instead of to modify it. Would you mind trying to downgrade the version? Much thanks!

[Alex @ Logical Invest]

Hi William, thanks, great you like it. Should work fine in Excel 2003, but once you open you need to confirm you want to enable the edit mode and allow makros (Standard Excel Messages). In some versions you need to adapt your ‘security setting’ to allow makros.

Here some links:
All versions: http://www.exceltrick.com/how_to/enable-macros-in-excel/
From our friends at MS: https://support.office.com/en-gb/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12

If this does not work, then I can share a downgraded version, but will not support some advanced features, so let’s try this first.

[Alex @ Logical Invest]

After getting the online version and the signal consolidation working had some time to work on the Excel:

Last Version:
Offline Excel Portfolio Builder V0.2 (overwrite, same link as above)

Updates:
– Incorporated the feedback received from Daniel M., thanks for that!
– Included 3x UIS into optimized portfolios, we’ll publish post and signals shortly
– ‘Efficient Frontier’ recalculated including 3x UIS and Friday data.
– Added MaxCAGR with <20% and <25% volatility
- Some smaller debugging on VBA

Based on Daniels' feedback, it seems that the MAC Solver does not like the VBA. So reaching out if there are other Apple geeks who might be interested in recoding a bit to make it run on Mac also. Either post here, or drop me a line.

Any feedback or interest to 'co-develop' is appreciated, just let me know.

[Alex @ Logical Invest]

Quick update today:
– Now including 3x UIS (SPXL/TMF) with synthetic 3x leveraged ETF backtest from 2002
– Updated efficient frontier accordingly to show the quite amazing new possibilities with only blending in 10-20% of this strategy.
– Also online builder will be updated.

Full post in progress with target this weekend.

Link same as above, and here a screenshot:

[rfm12]

Great job on this workbook. Couple of things:

If I refresh the quotes, I get #NAME? in the Volatility and Sharpe Ratio columns. (It’s already at the bottom of those two columns when I open the workbook.)

Also, when I open it, I get a message: “Solver installed. This workbook will work properly.” Any idea how to prevent that from appearing?

[Alex @ Logical Invest]

Thanks for testing and giving feedback!

The Solver message is a custom VBA I put in for testing compatibility of different Excel and Solver versions. So if there is a problem, or Solver is not installed, users get an error message, that’s the main purpose. Will take the ‘positve’ confirmation out in one of the next updates. Feel free to take the msgbox() line our in VBA for the time being, there is no protection in.

On the #Name error, would you mail me your workbook once quotes are updated? Most probably a date format or other region setting error. I’m working on a German Excel install with US region settings, but sometimes this just does not work as it should… (beside being a great excuse for my frequent typos..)

[[email protected]]

Alexander, just to make you aware I am running Office 2010 US version and everything is running fine

[jewillia]

The Excel Portfolio Builder is a very nice tool that I look forward to using, but I ran into couple issues. I am running Office 2010, but when I enable editing I get error message “Compile Error: Can’t Find Project or Library”.

Also, I compared the Excel version to a custom allocation I came up with and the results for CAR/MDD/etc are different between the Excel version and the Google Spreadsheet version. I can send screen shots if you want them.

[Alex @ Logical Invest]

Thanks for the feedback, glad you like it.

Yes, please send me your downloaded file + screenshots of the errors to [email protected]. As commented before, this is a bit of a debugging game due to different Excel versions and language settings – will take a while and need input from the US to China to Russia and a bit of Brazil if possible :-). Also pls try to open your solver manually, so to make sure it is properly installed. Should be on the Excel ‘data’ tab, or install in Excel settings.

The (hopefully slight) difference in the results comes from the different granularity of the online version. The more I would like to run it on daily data like in our backtester and the Excel version, it’s just to slow – so I’ll leave it with weekly data. The tests I’ve done show really marginal differences at the CAGR level, pls let me know if you see something different. At the MaxDD level they are higher, see here for more explanation: https://logical-invest.com/forums/topic/monthly-drawdown/

[Stefan Scherbarth]

Great spreadsheet, but some remarks about the concept:

The Portfolio Builder optimizes a portfolio with hindsight on mainly backtested system curves also created with hindsight. Therefore like any heavy system optimization the outcome is most likely not indicative for the future performance of the portfolio. Sometimes a heavily optimized solution turns out to be worse than a not optimized one.

To at least get an idea what the (hopefully positive) effect of the portfolio optimization is, I suggest to divide the time considered into an optimization period and a test period ( According to some standard procedure in system development).
Even then the result may be overfittet as long as the underlying system time series result from a back-test optimization, so the most strict approach would allow only real forward results without strategy change to be used to calculate the portfolio result. Unfortunately the periods are so far quite small for a good statistics, however I suggest to also indicate and ideally graphically mark the date where the respective portfolio shows real forward tested result.

Regarding draw down the maximum draw down is a quite bad quality indicator as it heavily depends on the time span selected and the events included. The monte carlo simulation of the equity curve is a well known and much better approach to fully exploit the information contained in a time series. Therefore it would be great if the calculation of the maximum portfolio monte carlo drawdown at a selectable percentil of the monte carlo time series would be available.

[Richard]

Hello Alex,

I began my subscription just yesterday. I’ve experimented with the Google sheets tool, accessing it through the blog, but I would like to use the Excel version off line on my own computer. I am not fully oriented to the LI web site so can you provide a pointer to the Excel tool that I can download?

[Alex @ Logical Invest]

Hi Richard, great, pls give me feedback if you encounter any bug or other difficulty, still trouble shooting this..

Here the link: https://logical-invest.com/wp-content/csvrepository/Portfoliobuilderexcel.xlsm

[Richard]

Hello Alex,

Thanks for the quick reply.

I’m running Windows 7 and Office 2007. I’m getting errors (#NAME?) on the bottom line for Volatility, and for Shapre Ratio. Also the “refresh quotes” macro “can not run”

As suggested elsewhere in the blog, I adjusted Excel Trust settings: “Enable all macros”, and “Trust Access to VBA project object mode” but without effect.

Is there a way I can include a screen shot into the blog response?

Thanks, Richard

[Alex @ Logical Invest]

Richard, just fixed the vola formulae which were not compatible with Excel 2007, pls try again. For fixing the ‘refresh quotes’ I’m a bit handicapped as I have no Excel 2007, pls send me some screenshots where the macro stopped (‘debug’ when error appears), or let me see your sceen live over skype (AlexanderHorn672), then I can update the VBA.

You can include pics posted on dropnote or other services using the ‘link’ functionality, or just send me to [email protected] and I post them.

Thanks for your patience, the different Excel versions are a bit hard to bring together..

[Richard]

Hello ALex,

I will update my Excel program to be compatible with your spreadsheet. While your spreadsheet is in Excel 2010, Office 2013 has overtaken 2010. Will Excel 2013 run your spreadsheet without extra tweaking?

Regards, Richard

[Vangelis]

Richard,

As far as I can tell, it works with Excel 2013.

Regards,
Vangelis

[Richard]

I switched to Excel 2010 to run the portfolio builder off line. I seemed to understand that among the outputs of the program is the allocation among the underlying ETF’S – is that the case? I seem unable to find that result. Also, is there a white paper that reviews the Portfolio builder approach and expected results and provides some guidance in its useage?

Thanks.

[Vangelis]

Richard,

The blog has some useful posts – use the search tool. For example:

https://logical-invest.com/mid-year-review-portfolio-builder/

https://logical-invest.com/new-fixed-weight-portfolio-optimization/

Regards,
Scott

[Richard]

Hello Alex,

Its been some time since I visited with the Excel Portfolio Builder and had been anticipating updates toward the Gold Version. The version I downloaded today was created on 6/27/15 – are there any new updates expected in the near term?

Thanks.

[Mark Faust]

Hi,
I have been looking at the offline builder.
I noticed it did not import 5/16 & 5/17 of this past week???

thanks
Mark

[Tony Walker]

Could you explain a little about the primary differences between the on and off-line portfolio builder. I have been running a few scenarios and comparing them and see differing allocations between them. Which is the recommended tool to use as of today?
Thanks for advice,
Tony

[Vangelis]

Hello Tony,
Alex addressed these differences here: https://logical-invest.com/forums/topic/online-portfolio-builder/#post-35434
“The differences between Excel and Online version come from the different timeframes, there is a three months off-set in the Excel tool, so some of the flat days in early 2008 are excluded, which increases the CAGR”
Hope this clarifies.

[Tony Walker]

Thanks, it’s clear.

[Alex @ Logical Invest]

Towards end of June we will update both the online and offline tools to include the newly launched US Sector Rotation Strategy as well, plus some other feature requests.

[Anonymous]

I have been using the offline Portfoliobuilderv40 and today updated to the latest version and to my horror it is very different. I can not seem to create a custom portfolio and the page that enabled that seems to have disappeared entirely? Am i missing something or have you made changes that eliminated the best feature of the entire offline portfoliobuilder?

Thanks,

[Alex @ Logical Invest]

Hi Larry,

don’t worry, everything still there, but currently doing an upgrade of the scripts which generate the data, and also the Excel and Online Portfolio Builder. Thought to finish by last week, but have some real headaches with some new functionalities..

So the current file will not update correctly, but this will be solved .. next days! Promise!

Btw, have you seen the “advanced” button on the first tab? This triggers the solver and some other tabs I hide for people who just use the pre-configured portfolios.

[Mark Faust]

[quote quote=45592]Hi Larry,
don’t worry, everything still there, but currently doing an upgrade of the scripts which generate the data, and also the Excel and Online Portfolio Builder. Thought to finish by last week, but have some real headaches with some new functionalities..
So the current file will not update correctly, but this will be solved .. next days! Promise!
Btw, have you seen the “advanced” button on the first tab? This triggers the solver and some other tabs I hide for people who just use the pre-configured portfolios.
[/quote]

I am waiting for the new version as well…..old one stops in Feb of 2016…
Currently using the strategy signals to track ROI….(although there is not a 3XUIS there?)

Mark

[Alex @ Logical Invest]

Here the new version: https://logical-invest.com/?gfid=15

Changes:
– Data structure updated back to Jan 2008
– Included US Sector Strategy
– Portfolio Optimized for period Jan 2008 – Sept 2008

Pending:
– Post about newly optimized portfolios, now including US Sector Strategy

Help Needed:
– Have been battling with this now for two weeks, maybe somebody has a good idea: For the Portfolio Optimization want to include a min allocation and step size, e.g. for a min 5% and step size 5 possible allocations can be 5%, 10%, .. 95%, 100%. Sounds easy, but requires in Solver:
– Only integers e.g. allowed – this slows solver by factor of 5x
– The min allocation still requires 0% to be allowed for strategies with no allocation, this is not trivial to be
implemented with the constraints, and adding checks in cells slows down-
– Leads to corner solutions, e.g. solver does not find best solution, but sticks to a sub-optimal solutions

Idea for this is twofold:
– For smaller accounts a 2-3% allocation does not make sense from a transaction cost perspective
– For smaller and medium account sizes, it makes no significant difference whether 10%, 9%, or 8% are allocated to a strategy, this is a false accuracy which is easily eaten up by the estimation error of the optimization approach, thus think a 5% or even 10% step size might make sense.

Current solution runs, but not stable and a simple run for one portfolio takes >10 min. If there is no feasible solution using the solver will try a brute-force approach, but this will also be painfully slow..

[reuptake]

Is it possible to include backtests of “new” versions of strategies in Portfolio Builder? I mean new, less “aggressive” GMRS and so on? This is – in my opinion – far more important to estimate the performance in future.

BTW: for your problem: if this is hard, I doubt if it’s worth the effort. If someone gets eg. 1%/32%/49%/17% he can manually check if 0%/30%/50%/20% gives similar results and stick with it.

[Alex @ Logical Invest]

We will only include the strategies we offer as signal service. For all others you need to use QuantTrader for building a custom meta-strategy.

And you are right, probably I was just trying to please my ego with the advanced optimization :-)

[reuptake]

[quote quote=45995]We will only include the strategies we offer as signal service. For all others you need to use QuantTrader for building a custom meta-strategy.[/quote]

I meant exactly this: current versions of strategies offered as signal service, not “previous” versions, not longer offered.

[Patrick @ Logical-Invest]

Several of the strategies do not have complete return data. The data starts on 3/12/2008, but MYRS starts on 1/4/2011, 3x UIS starts on 2/1/2010, WTOP4 and NASDAQ100 start on 8/3/2009, GLD-USD starts on 3/3/2009.

It would also be helpful if all data sets could go back to at least 2007 to see how they reacted in 2008.

[jewillia]

A friend of mine looked at the offline Excel Portfolio Builder spreadsheet in a lot of detail and he found a small error in the calculation for CAGR. The error is due to how the time difference from End to Start is calculated. A Named range
called “DatesDuration” is used to calculate the time difference. The spreadsheet uses a fixed amount of time to count
the number of rows of data, which gives trading days, but the formula needs to use calendar days. Currently the
spreadsheet is adjusting the time by adding 5 * 100, there are approximately 100 days/year that are non trading,
and this adds 5 years of this. But since there are more than 5 years of historical data, it causes CAGR to be overstated.
One should use the difference between the start and end date, and let it change every time the data is updated.

Here’s a fix for the calculation:
Unhide the sheet named “Control”
Unprotect it
In cell B4 you will see the name “DatesDuration” . Need to change C4.
In cell C4, put in the following formula.
=MAX(ReturnData!A:A)- (MIN(ReturnData!A:A)-1)

[Alex @ Logical Invest]

Hi Patrick,

correct, we only publish signals from the inception day of the earliest available ETF, in many cases either TMF, ZIV or other one limit the availability. We could use synthetic data, but due to potential inaccuracies have so far opted not to do that. For all strategies but MYRS we have published these backtests as separate posts, MYRS does not make much sense before 2011 as only then with the inception of the volatility ETF the vola trading became popular for retail investors. Looking into 2008/2009 of course would be interesting from an academic perspective, but is of little practical use.

[Alex @ Logical Invest]

Thanks for the comment, appreciate you’re looking deep “under the hood”!

Indeed there is a fix needed, but bit more complicated so let me have a detailed look and upload a new version. The formulae was intended to adjust the effective trading days for the strategies which started after 2008 (MYRS, 3xUIS, GLD-USD), but indeed now change all CAGR. The overall effect is small, but indeed needs to be corrected. Give me some days..

—————
P.D. Always appreciate further input or enhancements of the Excel tool, this is really meant as a toolbox for the community, so keep it going, Excel gurus out there ….

[Mark Faust]

Alex,
Is there a reason that the Excel version lags so far behind in updating quotes??
I just hit the Refresh Quotes button and then opened the advanced column so I could see the extra worksheets..
(I specifically use the Return Weighted worksheet to track the daily returns when I am calculating Mean, Standard Deviation and Sigma values. I am trying to find a good Meta Strategy.)

Problem is, for the last 3 days, it only will refresh back to 11/24….That is 5 days ago….
While I love this Excel version, it seems to always have trouble doing daily refreshes….

Any thoughts on this?

thanks
Mark

[Alex @ Logical Invest]

Hi Mark,

thanks for the hint, seems the input file caching was too long, so you always pulled an old version. Have updated now and just pulled with last date 11/29/2017, please give it a try to see if it works properly.

The input file from our server is this here, you can also use for other purposes or to validate it should add the last day about mid-night after last close: https://logical-invest.com/wp-content/csvrepository/PortfolioBuilderExcel2.csv

All the best,
Alex

[Mark Faust]

Thanks for the quick response and link to file.
I will have a look and see if I can finish polishing up a decent combination of strategies for next month (and going forward)
Mark

[quote quote=47836]Hi Mark,

thanks for the hint, seems the input file caching was too long, so you always pulled an old version. Have updated now and just pulled with last date 11/29/2017, please give it a try to see if it works properly.

The input file from our server is this here, you can also use for other purposes or to validate it should add the last day about mid-night after last close: https://logical-invest.com/wp-content/csvrepository/PortfolioBuilderExcel2.csv

All the best,
Alex

[/quote]

[Author]

Posts