portfolio analysis, python, investing, early retirement, financial independence, stocks, moose, msolife

Portfolio Analysis with Python: Move Over Excel

Portfolio Analysis with Python

If you’re like me, you’ve used Excel for a long time. Excel is excellent at creating budgeting spreadsheets or building a net worth tracker. I’m not dumping on Excel; it’s a loyal friend to most of us. However, there’s an area where Excel falls short and is incredibly weak: portfolio analysis.

Learn what portfolio analysis is and how the Python programming language can help you below!


Believer it or not, I once used Excel to manage hundreds of millions of dollars that were invested in hedge funds and private equity, as well as publicly traded stocks. I worked for a small firm with a limited budget and, aside from one sole Bloomberg terminal (only $25k per year!), we abused Excel and asked it to perform miracles on a daily basis.

Excel is excellent at a lot of things, as I mentioned in the intro. However, it’s terrible or downright inferior at performing some important tasks. This is where Python steps in.

How I Learned Python

Python is one of the more popular coding languages in use today. It’s highly flexible and adaptable, much like its namesake.

I’ve tried, unsuccessfully, to learn Python from books. After several years of starting and quickly losing interest, I knew I had to approach this task from a different angle. Learning code via books is like learning to shoot a basketball by reading a magazine; it’s lame and unlikely to meet with success. At least for me.

So, instead of continuing to do the same old thing and fail yet again, I decided to take online Python video classes. There are a lot of resources out there, but here are the ones I have personal experience with and recommend:

  1. Khan Academy
  2. Coursera
  3. Udemy

While learning from videos is no walk in the park, I found it to be infinitely more stimulating, and I retained knowledge far better than trying to code from a book.

I kept at it until one day, it clicked, and I knew how to program. I “embraced the suck” and kept plugging away, sometimes to the point of exhaustion, until I could get something up and running.

Related: Embrace the Suck

Portfolio Analysis

Portfolio analysis mainly revolves around applying academic theory in a real-world context. I’ll go over some of these concepts and the math behind them if there’s demand for it (leave a message in the comments!), but for now, I’ll go over what I did. To be honest, I did so much of this in business school and at work that I still get bored talking about the nuances and calculations. So, I’m outsourcing the in-depth explanation to Investopedia.

Do I believe that portfolio analysis will always guide you to the best solution? No. However, there is no better alternative that I know of to manage BILLIONS of dollars at a time. It’s an educated guess that you can then round out with further research and experience.

The Setup

While you could technically code in Python in a text editor, using a development environment is infinitely preferable. It helps you debug your code, organizes it into a more readable structure, and lets you get help when you need it. I use the Jupyter Notebook platform in the Anaconda (get it, because Python?) developer environment.

Here’s what it looks like:

anaconda, python, portfolio analysis

Pandas (what’s with all the animal names?) is a robust software library that’s invaluable when it comes to data analysis. Quandl is an online data service chock full of remarkable data.

I “imported” both Pandas and Quandl so that my code would execute; the commands are not native to Python itself.


Since I’m still learning, I had a simple situation to analyze: invest in four tech stocks. The stocks are Apple (AAPL), Amazon (AMZN), Cisco (CSCO), and IBM (IBM). I “invested” $1 million total into these stocks, and I want to find out how a portfolio of those stocks weighted 30%, 40%, 20%, and 10% (respectively) would perform.

Total Portfolio Value

The result is this:

portfolio analysis

Unfortunately, the data I was pulling from Quandl in 2018 was all sorts of jacked up and unworkable. Told you I’m new at this. So I used a different date range than I originally wanted.

As you can see above, $1 million invested in those stocks, in that ratio, would be worth almost $3 million five years later!

But wait, how did the individual stocks perform during this time frame?

portfolio analysis

Wow. Almost all of the portfolio return is driven by Amazon! IBM looks utterly tepid by comparison.

Distribution of Portfolio Returns

What if I want to see how often I can expect certain daily returns in this portfolio? No worries, I got you covered. Check out this histogram:

portfolio analysis

As you might expect, the returns follow a normal distribution, more or less. It was extremely rare for the portfolio to be up 5% or down 5% in any one day. However, it was close to a 0% return quite often.

Let’s Get Fancy

What about the returns for each stock? Can we see a histogram of those? Sho nuff.

portfolio analysis

Here’s where stuff starts to get interesting. We see the same normal distribution that we saw in the overall portfolio. No shock here. However, notice that some stocks don’t have as much symmetry of returns as others.

Amazon had one day where the stock returned almost 15%! Conversely, lame old IBM couldn’t get much past a 5% return in a day.

Related: Social Insecurity

Going Deeper

Next, I calculated the expected return, expected volatility, and Sharpe Ratio of different random combinations of these stocks in a portfolio. Huh?

Expected return is relatively simple: what can I expect a portfolio with a certain combination of these four companies to return?

Expected volatility tells me how volatile I can expect these returns to be on a day to day basis. You see hints of this in the histogram, but this gives me the cold hard numbers.

The Sharpe Ratio is the average extra return you’re getting on top of the risk-free rate (10-year treasuries, typically). You can read more about it here, if so inclined.

I prefer the Information Ratio since my returns are judged relative to a benchmark, but I’m a programming noob, so I’ll tackle that later.

This is what the output looks like:

portfolio analysis

The highest Sharpe Ratio I can hope to achieve is around a 1.04. Anything over 1.0 is acceptable, so we’re on the right track.

The “2468” refers to the 2,468th random portfolio weighting. I had this code run 10,000 random simulations to figure out the best possible combination of those four stocks. Guess how long it took? Seconds. If you tried this in Excel, it would melt your hard drive.

This is telling me that 15.5% Apple, 16.2% Cisco, a puny 0.4% IBM, 67.9% Amazon is the portfolio with the highest Sharpe Ratio over this time span. Go Amazon! Amazon’s price/earnings ratio is an abomination, but let’s roll with it.

Paint Me a Picture

I’m a visual guy. That’s how I learn (online videos), and it’s how I’m trained to see the world (photography). These numbers are nice, but show me what this means!

portfolio analysis

Daaayum. See that curve? This is what’s referred to as the “efficient frontier.” If you pick a spot on that line, you’re maximizing return for a specified amount of “risk” or volatility. Risk, to me, isn’t volatility, but academically speaking, this is acceptable. As you can see, higher volatility (risk) correlates with a higher return (reward).

Toward the lower left of the curve, you see a point where it doubles back over itself. You do not want to be the schmuck taking on the same amount of risk for a lower return. Don’t be that guy (or gal)!

Here’s a clearer view of that efficient frontier:

portfolio analysis

The red dot with a black border in the top right quadrant of the first graph represents the “optimal portfolio.” This is the point where the Sharpe Ratio is maximized, and the portfolio has a “perfect” balance of risk and reward.

EVERY SINGLE ONE OF THE 10,000 RANDOM PORTFOLIOS IS GRAPHED ABOVE! I find that impressive, maybe I’m weird.

Related: Inverted Yield Curve: Stock Market Crash Predictor?


This exercise is highly academic. There is a vast gulf between what academics do and what actual hedge funds (practitioners) do.

For example, risk does not equal volatility in my eyes. Risk, for me, is seen more in the financial statements of these companies, not the daily movements of their stock prices.

Additionally, these results are backward-tested. The past does not equal the future. Making a portfolio to match the optimal portfolio will likely not give you the best results going forward. However, it’s a good starting point, and you’ll probably do better than randomly guessing. That’s something, I suppose.

Despite the flaws in trying to make your map the landscape and not vice versa, most large pensions and endowments continue to use these and other similar techniques to manage portfolios. Like I said earlier, there isn’t a widely-known alternative that has proved to be better yet.

So Why’d You Show Me This???

First, it’s interesting if you’re a finance geek and I’m guessing you might be.

Second, the lesson here isn’t the content. It’s the fact that you can leverage powerful computational tools to better manage your money. 

Sure, investing in passive index funds is the way to go. Ideally, you also own some cash-flowing assets. But what if you want to figure out how much international exposure you should have, how much you should hold in bonds, and how much “growth equity” you need? If you’re a tinkerer like me, do yourself a favor and use portfolio analysis to point yourself in the right direction. 

Related: Liquidity Mismatch, the Hidden Risk of Mutual Funds and ETFs

Doing this scratches the itch to try to beat the market without doing too much damage. I’d rather invest in a portfolio that I’ve optimized in this fashion over picking 20-30 individual stocks.

Related: Should You Invest in Individual Stocks? Probably Not.


Look, passive investing is still the way to go for most of us. I’m not saying it’s not. However, I have the kind of personality where I like always to be researching things and testing out ideas. This is extremely dangerous to your nest egg, potentially. So, at least put in the work if you suffer from “tinkeritis.”

Right now, I’m 100% passively invested because I’m on a mini-retirement. I’m not messing around with my nest egg, and I’m keeping a good amount of liquidity on hand. But this stuff is interesting to me, and I think some of you will get a kick out of it.

These operations would cause Excel to crash, and I’m personally excited to learn about powerful tools you can use to understand better how the mega-investors invest. 

Related: A Taste of Early Retirement

Related: Liquidity Ladder: Survive a Long Term Loss of Income

Let me know what you think!



One Response