- 03/24/2015 at 2:42 am #18514
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@example.com to post.
Offline Excel Portfolio Builder V0.1
– 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 site03/24/2015 at 4:54 am #18531hawaiianwaveriderParticipant
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.03/25/2015 at 2:42 pm #18572William WongParticipant
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!03/25/2015 at 9:13 pm #18575
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.03/30/2015 at 2:35 am #18844
After getting the online version and the signal consolidation working had some time to work on the Excel:
Offline Excel Portfolio Builder V0.2 (overwrite, same link as above)
– 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.03/31/2015 at 10:59 pm #18923
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:04/02/2015 at 5:28 am #19049rfm12Participant
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?04/02/2015 at 12:25 pm #19076
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..)04/02/2015 at 3:05 pm #19081[email protected]Participant
Alexander, just to make you aware I am running Office 2010 US version and everything is running fine04/26/2015 at 11:52 pm #20249jewilliaParticipant
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.04/27/2015 at 12:13 am #20253
Thanks for the feedback, glad you like it.
Yes, please send me your downloaded file + screenshots of the errors to firstname.lastname@example.org. 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/04/29/2015 at 8:30 am #20726Stefan ScherbarthParticipant
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.05/09/2015 at 12:20 pm #22064RichardParticipant
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?05/09/2015 at 12:33 pm #22065
Hi Richard, great, pls give me feedback if you encounter any bug or other difficulty, still trouble shooting this..05/10/2015 at 1:04 pm #22163RichardParticipant
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?
- You must be logged in to reply to this topic.