Rev. Date December 11, 2023

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 five workflows commonly performed in Excel:

1. Factor analysis

2. Investment evaluation

3. Model portfolio construction

4. Portfolio stress testing

5. Portfolio optimization

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 analysis.

Exhibit 1 | Illustrative Examples of Factor Analysis in Excel vs. Venn

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. Furthermore, the entire spreadsheet needs to be updated and re-configured on a monthly or quarterly basis as you receive recent performance data.

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?

Exhibit 2 | Illustrative Examples of Manager Evaluation in Excel vs. Venn

If you need to propose changes to your organization’s portfolio or you are a Wealth Advisor and want to propose a solution to your clients, you may want to know how adding or removing investments or changing asset allocations impact the overall portfolio performance. This workflow in Excel is a time-consuming process that involves data aggregation of all potential investment options and modeling multiple portfolio return streams with varying asset allocation weights.

Once the portfolios are properly configured in your spreadsheet, all the analysis and calculations described previously will need to be written. After looking at the results, any additional changes would require a repeat of all of the above steps.

Venn can help you construct your baseline portfolio and enable you to quickly add or remove investments and change asset allocation weights. Venn will re-run all analyses promptly after you have made the changes so that you can see the impact right away and make any additional changes as needed that will update automatically. You can also compare different portfolio versions side by side across various metrics and analytics available in Venn to measure the impacts of the changes and build your narrative for the proposal.

Exhibit 3 | Illustrative Example of Model Portfolio Construction and Analyzing Allocation Changes in Venn

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 portfolio[3] that is run through history to identify when your portfolio may have experienced a drawdown. 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 Lens[4] 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 4 | Illustrative Examples of Drawdown Analysis in Excel vs. 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 5 | Illustrative Examples of Portfolio Optimization in Excel vs. Venn

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.

REFERENCES

[1] As of the date of this article, the history of returns of the factors in the Two Sigma Factor Lens extends to October 1997.

[2] Venn’s data uploader allows users to add investment returns to the platform in different ways. For example, users may copy and paste dates and returns, upload files such as csv, or forward files to a Venn returns inbox for processing. Additionally, Venn provides users with a pre-populated library of thousands of mutual funds, ETFs, indices, and equities that do not require users to upload investment returns to run investment analysis.

[3] A factor-mimicking portfolio is constructed using the portfolio’s factor exposures multiplied by the historical factor returns.

[4] As of the date of this article, the history of the returns of the factors in the Two Sigma Factor Lens extends to October 1997.

[5] Excel’s Solver may be configured to allow users to use forward-looking return expectations as well.

This document highlights certain aspects of this feature. As an overview, it does not discuss all material facts or assumptions. Please see Important Disclosure and Disclaimer Information.