Microsoft Excel is a powerful tool that, while not specifically built for investment and portfolio analysis, is used broadly by allocators to help with everything from asset allocation to risk monitoring to pro forma portfolio construction. Venn, a factor-based analytics tool, was designed specifically to help institutional allocators improve their portfolio and investment analysis. This guide shows how you can use Venn to conduct four workflows commonly performed in Excel:
1. Factor analysis
2. Investment evaluation
3. Portfolio stress testing
4. Portfolio optimization
Analyze factor exposures and return and risk contributions in Venn
Excel’s Regression tool (available through its Data Analysis plug-in) can be used to run regressions on portfolios or investments. Managing your portfolio or investment data in Excel in addition to the explanatory risk factor data can be time-consuming and labor-intensive. You may spend hours updating portfolio returns, finding the right explanatory factor data, making sure the dates are aligned, etc., before running a regression.
All of this preparation yields a regression output called an “Analysis of Variance” (or ANOVA) table that is confusing, static, and only representative of a single period. What if a month of data is appended to the return stream? You would have to run the manual regression all over again. What if you want to see rolling regression results over time? This requires navigating INDEX and LINEST formulas. What if you want to see contributions to return or risk? This necessitates additional, manual calculations.
Venn can display dynamic regression output without as much effort. All you need to do is upload your investment or portfolio returns (or use Venn’s pre-populated library of equities, mutual funds, ETFs, and indices), and we provide the factor lens. The end result is a summarized, intuitive regression output, such as factor exposures and factor contributions to risk and return, that is customizable over any time frame during the overlapping period between the investment or portfolio return history and the Two Sigma Factor Lens return history.1 In addition, you can see how these results change over time by viewing Venn’s Factor Trend tool.
Exhibit 1 | Illustrative Examples of Factor Analysis in Excel vs. Venn
Source: Excel and Venn Tearsheet. April 2019.
Upload investment returns to Venn to view a robust tearsheet
Excel’s strength is its power with numbers. It’s relatively simple to run analysis on one investment using Excel. If you ask a potential manager for a time series of their historical returns, the manager will likely send over an Excel spreadsheet with the dates in one column and the returns in the next column, which you can use in your analysis without much further configuration.
If running a multi-investment comparison, it becomes tedious to properly align the return data over the same time frames. The same issue occurs when aligning a potential investment with the historical returns of your existing portfolio to analyze how an allocation to that investment would have affected your portfolio historically.
Once the data is properly configured in your spreadsheet, the analysis can begin. This may involve writing complex formulas that can be prone to errors. As your spreadsheet grows in size and complexity, it can be frustrating to discover a mistake in your analysis that requires you to spend time debugging your calculations.
Venn generates an adaptable and customizable tearsheet for an investment promptly after you have uploaded its returns.2 You don’t have to be a spreadsheet guru to get value from the outputs on the tearsheet, which include performance, factor, and correlation analyses.
Venn can help you identify whether an investment is worth considering by answering questions like, how has this investment performed historically? What are the major Two Sigma Factor Lens factors that this investment is exposed to? Has this investment possibly exhibited style drift over time? Is the investment potentially additive to my portfolio? Does it appear to provide a unique return stream based on its correlation with other investments in my portfolio?
The investment tearsheet in Venn allows allocators to spend more time evaluating and analyzing rather than configuring and calculating.
Exhibit 2 | Illustrative Examples of Manager Evaluation in Excel vs. Venn
Source: Excel and Venn Data Uploader and Tearsheet. April 2019.
Try running your portfolio through Venn’s drawdown analyzer
If you’re a risk manager, you may want to know what would happen to your portfolio if the market conditions that characterized the Global Financial Crisis of 2008 or the Quant Crisis of 2007 were to repeat. Specifically, you may want to understand which historical periods, if repeated today, could create a drawdown in your current portfolio. Answering these questions in Excel is a time-consuming process that involves similar data aggregation, alignment, configuration, and modeling as described previously.
Venn’s Drawdown Analysis can provide this analysis in a simpler, more automated manner. By taking your pro forma portfolio’s current Two Sigma Factor Lens factor exposures, it creates a factor-mimicking portfolio3 that is run through history to identify when your portfolio may have cracked. You can select a specific drawdown magnitude (e.g., -10% or -20%) and quickly receive a list of all time periods in the history of the Two Sigma Factor Lens4 where your portfolio’s returns would have exceeded that threshold.
In addition, Venn provides market color on what was happening during each period. Venn helps you answer questions like, how did the Two Sigma Factor Lens perform during this time? How did the factors’ correlations change versus their historical averages? What specific investments or strategies contributed to your portfolio’s drawdown?
Exhibit 3 | Illustrative Examples of Drawdown Analysis in Excel vs. Venn
Source: Excel and Venn Drawdown Analysis. April 2019.
Optimize portfolios based on your objectives and constraints through Venn
Once all of your investments’ returns are in the same Excel spreadsheet and properly aligned, you can create a weighted return stream (with some assumption for rebalancing) that represents your portfolio. You can use Excel’s Solver add-in to set an objective like maximizing the historical return of that portfolio by changing the weights to the various investments.
Venn’s flexible optimizer allows you to run optimizations with customized objective functions. Advanced settings within optimization allow you to easily specify real-world constraints. For example, what if certain investments are “locked up” such that their allocation can’t change? Or what if you can only allocate up to a certain dollar amount to an investment? All of these constraints are easily configurable on Venn.
Additionally, you can set your factor exposure goals. Whether you would like your portfolio to achieve higher exposure to certain factors or reduce exposure to others, Venn’s optimizer can help you incorporate these objectives into your analysis.
Further, Venn’s optimizer uses your forward-looking return expectations. What worked in the past might not be indicative of your future expectations. On Venn, you can configure your forecasts such that the optimization results best reflect your view of the future.5
Finally, the output of Venn’s optimizer is more readily actionable than Excel’s output. Rather than displaying the change in weights to certain investments like in Excel, Venn generates a specific trade list for each investment to help you understand how you can move closer to your optimal portfolio.
Exhibit 4 | Illustrative Examples of Portfolio Optimization in Excel vs. Venn
Source: Excel and Venn Optimization. April 2019.
Help make your portfolio analysis and investment evaluation more efficient by using Venn. Expand beyond static output, complex formulas, and data configuration struggles. Discover an easier, more intuitive way to uncover insights about your portfolio that can spark conversation among your team and with your managers.
Try Venn for free today.