When I was young, I made the mistake of getting a degree from a business school instead of an engineering school. It did serve to make my academic life very easy, but part of that was because we didn’t learn anything that instructors deemed “too difficult for a business student”.

So, imagine my surprise when I got into the world of professional trading, and found out everyone was using Python and not Microsoft Excel.

I had to get good. And fast.

The Lies of Business School

It still sort of irritates me that I went to college for Finance/Economics and not Computer Science. Not that I don’t feel like “a real programmer” – most good programmers I know didn’t even go to college in the first place – but rather that I feel business school genuinely hindered my learning of how the real world worked, rather than helped it. 

I have a lot of examples and stories of this, but the focal point of this article is one that is rather small and petty. You see, the Finance department did have a mandatory data analysis class. It was a single semester, and spent the entire time talking to us about Microsoft Excel. No mention of any programming – not R, not Python, not even SQL of all things. They phrased the entire class as if all people ever did in these analysis jobs was use Excel: add data to Excel, crunch data in Excel, make visualizations in Excel. Hell, now that I’m remembering it, I don’t even think we learned DataViz software like Tableau. It’s fucking Tableau, for christsakes! It’s not rocket science!

So, obviously, with my teachers telling me all this, I assumed the only data I’d ever be working with was in Excel. Now, don’t get me wrong – there’s plenty of jobs that don’t deal in datasets large enough to use anything but Excel, and honestly I envy them (If you’re hiring for one of these jobs, let me know. I’d love to use what I actually learned in school). But most real jobs either use massive datasets requiring knowledge of query language, or utilize APIs to get their data. 

This, as it turned out, included finance jobs.

My first work requiring real-world finance knowledge was still at the school, but as an analyst on the endowment’s investment team. Obviously these guys dealt with real money – large amount of real money – and so they weren’t fucking around. After getting the job, I learned that this role – despite being heavily marketed towards finance students – usually only hired computer science majors since it required knowledge of derivative mathematics [1] and the ability to use the APIs of Bloomberg and CapIQ [2]. The only reason I had gotten the job was because I had heard of R and Python and had the sense to add them to my application alongside every Python data package I knew. I didn’t actually know how to use any of them.

You’d think that during this job I would have learned to use these skills and become a better, more well-rounded person. Now to my credit, I did try to do this – I was the only person in the entire class [3] who read all four recommended textbooks in full – but it all went way over my head. I ended up just letting the CS guys do all the work while I nodded in the background and pretended to know a single thing they were saying. 

So at this point you’re probably wondering what the title was all about. Obviously the $100k algorithm isn’t the endowment, because it was all written in Python in the first place and there was no need to convert it. Instead, this algorithm came from a poor bloke who was trying his hand at professional trading and quickly learned why all these professionals were using Python to begin with…

That’s right: the $100k+ trading algorithm was my own personal funds. 

Why Excel Worked (Up to a Point)

Skip a few years later: I got my first job out of college (in tech, after I learned business/finance were all around terrible) and got my first $100k. Given my lifelong interest in trading, I only put half of it in a SPY IRA and the other half I wanted to use for testing algorithms. So I guess it’s more of a $50k algorithm, but that doesn’t affect what we’re talking about.

Since I never had any formal experience in Python, I built the entire thing in Excel – more specifically Google Sheets, where I could use the Google Finance API [4]. This meant I had to pre-screen my stocks on a different software (I chose Koyfin) and I also… well… couldn’t backtest. The lack of proper backtesting was a pretty big black hole – it was something cemented in my investment learning as essential, but I figured that with the amount of capital I had (and my care when it came to risk) I could just stick with well-known trade methodologies and be fine.

Besides all that, it worked! Google Finance would update the close/low/high listings, and my functions would do the rest. As long as I stuck to the same suite of 100-150 stocks (since I was trading ETFs this wasn’t a problem) the system would work well enough. But as I would end up finding out, “well enough” in trading still isn’t enough to make the difference.

The number one issue with the spreadsheet approach – and the reason I ended up finally moving on from it – was that it wasn’t practical to use historical data, something that pretty much every trading algorithm relies upon. Sure, you could use historical data if you wanted to log in every morning and spend four hours downloading CSVs from Yahoo and implementing them into your model. But I sure as hell didn’t. 

It got to a point where my lack of being able to use historical data was getting pretty ridiculous. I knew that, ideally, I wanted both my portfolio weights and stops to be dynamic and automated by the system. On paper, this is a very simple operation to do. But if you were using the manual spreadsheet process, it’d require hours of tedious labor that most traders these days figured out how to automate by just learning Python.

In other words, it was time to learn Python. 

The Python Conversion + Lessons Learned

Funnily enough, I think my trading algorithm is the only thing I’ve coded without any help from an LLM. That’s mostly because I assumed that my data package of choice – yfinance – was too obscure for an LLM to have accurate knowledge about. I later found this is the standard for pretty much any hobbyist trader and ChatGPT knows it inside out.

Before we got to the Python conversion proper, we needed to figure out what algorithm we wanted to make. For me, it went something like:

1. Screen a subsegment of ETFs (same as before – this is still done outside the algorithm, but takes virtually no time to do)
2. Find the Kelly weights of chosen ETFs (NEW – this requires historical data from your trading window (mine is 30 days). Without getting too technical, the Kelly criterion weighs your stocks by the historic frequency and magnitude of winnings. You can read this as: stocks with a long, rich history of making money get first dibs in your portfolio) 
3. Build dynamic trailing stops (NEW – you can set a generic stop (usually the previous day’s low) but it doesn’t account for stock volatility as well. Historical data can be used to fix this. To be fair this was a later addition and I did have ChatGPT help on the math for this)
4. Export the whole thing as a CSV so that I can easily read it and submit the trades (standard practice – most brokerages even have a way to read CSVs for you, but I like the manual process here)

Now, like I said, this is pretty easy: we aren’t doing any advanced calculus or reinforcement learning to get our portfolio churning out results. Because of this, the only real packages I needed were yfinance (as mentioned previously), pandas (to mess with the data from yfinance), and numpy (a prerequisite for pandas). All I have to do is cycle through the ETFs, download 30 days of data (open/high/low, as it was in the spreadsheet) and do the calculations.

One challenge with programming is that, unlike spreadsheets, you don’t get immediate feedback. With Excel I can see the data right in front of me, so if I make a mistake or error it’s immediately apparent. With Python, you’ve gotta do things step by step: spend too much time crunching the numbers and your output might lead to nonsense with you having no idea what you did wrong.

Fortunately, Python users already solved this problem with something called Jupyter. Jupyter is a way of converting your code into snippets so you can playtest pieces of it over time. It’s considered a de-facto standard in the data analysis world, and while I had known about it I really didn’t come to appreciate how useful it was until this project.

While it took a few tries, I was eventually able to get the whole thing working. It takes in a CSV (the pre-screened ETFs) and then 1) calculates the Kelly weights, 2) adjusts it to the portfolio (given the weights and our cash, how many should we buy of each), 3) creates volatility-adjusted trailing stops for the chosen stocks, and 4) exports the output as a CSV with the ticker, the amount of shares to buy, and the stop.

Interested in the specifics of the code? I have good news! The entire algorithm is free and open-source, and you can look at it here. This is indeed the exact version I use for trading, and I update it whenever I make updates to the live algorithm. 

Next stop… well, uh… oh dear, I never did do backtesting, did I? You can look forward to that one in a later article.

[1] – That is the mathematics behind financial derivatives, not derivatives in general. It is a bit more complicated, requiring knowledge of concepts like Brownian motion and Ito calculus. I did not know this (I still do not know this) but I pretended to throughout the entire job. 

[2] – I’ve never come across this ever before or after getting this job, so I feel the need to explain: CapIQ is S&P’s competing software to Bloomberg. You can read this as “The university didn’t want to pay for Bloomberg and instead got the cheaper CapIQ but then all the endowment guys complained about it so they bought Bloomberg Terminals anyway and now we had subscriptions to both which was a total waste of money and resources but no one bothered to fix it”.

[3] – Like most jobs, once you got in you had a “training academy” that would teach you the basics behind what you were going to do in-role. I didn’t learn anything from this either. 

[4] – Yes, obviously Microsoft Excel and Google Sheets aren’t the same app, but they might as well be. Take it as another simplification for the post title.