Excel for trading research?

Ever met a quant who was slightly religious about their tools? I’ve had Python zealots, R devotees, and MATLAB missionaries (well, back in the day, at least) all claiming their way is the best way.

I use R for most of my analysis. The tidyverse libraries make sense to my brain, and I can move fast with them. But here’s the thing – the tool doesn’t matter nearly as much as what you do with it.

Let me share something that might surprise you: Excel can be a powerful weapon in your trading arsenal. Yes, that Excel.

I recently used Excel to dig into an interesting claim about gold prices – supposedly, gold shows unusually high returns on Fridays and tanks on Mondays. Sounds like the kind of market folklore that usually makes me roll my eyes, but let’s see what the data says.

I’m naturally sceptical of these patterns.

Usually, I want to see a solid reason why an effect exists:

  • Risk premia? Makes sense – humans are risk-averse.
  • Pairs trading? Sure – you’re pushing prices back to fair value.
  • Turn-of-month effects? Yep – predictable rebalancing flows create those.

But gold being consistently stronger on Fridays? I can’t think of a convincing reason why that should happen (perhaps I’m revealing my economic naivety).

Still, data doesn’t lie (well, except when it does, but that’s another email). Here’s what I found:

  1. Friday returns are notably higher than other days
  2. Monday returns are negative – the only day showing negative returns
  3. The effect has been surprisingly consistent over time
  4. The pattern looks decidedly non-random

Despite the compelling data, I’m still not entirely convinced. Why? Because without understanding why something happens, you’re more at risk of the effect disappearing tomorrow.

If I were to trade this (and I might), I’d:

  • Only play the long side on Fridays
  • Skip the Monday short (returns are too small vs costs)
  • Size it small (acknowledging the lack of fundamental backing)
  • Run proper backtests with realistic costs

The broader point here isn’t about gold seasonality – it’s that I used Excel to gain some useful insights about this effect.

Excel let me:

  • Group and analyze returns by day of the week
  • Track consistency across different time periods
  • Visualize the cumulative effect as a time series
  • Poke and prod the data from different angles

All without writing a single line of code.

Here’s a quick run-through.

The data

The hard part of putting this spreadsheet together was getting the data.

We pull price data for a user-specified ticker from Yahoo Finance using Excel’s data model. The details aren’t important here; we’ll focus on the analysis instead. I mostly want to give you a sense of how easy it is to do data analysis in Excel.

The data for GLD looks like this:

We have columns for date, OHLCV and adjusted close, all of which were pulled from Yahoo Finance. We calculated some additional columns for our analysis:

  • Log returns
  • The day of the week
  • The month
  • The year

Day of week seasonality

Let’s see if what were told stacks up – has gold historically shown higher returns on Friday and lower returns on Monday?

To figure this out, we can simply create a pivot table (Insert –> Pivot Table), put the day of the week on the x-axis, and put the sum of the log returns on the y-axis.

This performs a group-by operation: create five groups of our log return data (one for each week day) and then add up the contents of each group.

You get some numerical output:

But a chart makes for easier interpretation.

Simply select one of the cells containing numerical output, and then from the Insert menu, select Bar chart.

The results look like this:

Indeed, we see anomalously high returns on Friday.

We also have negative returns on Monday. This is unusual because Monday is the only day showing negative returns. However, the magnitude of the negative return aligns with what we see on Tuesday through Thursday.

Making a single metric

I like to get a sense of how the effect has changed over time.

In particular, I’ll be quite excited if it’s been unusually consistent – although, to be honest, nothing ever looks as consistent as you’d like, and if it did, I’d assume I’d made a mistake in the analysis!

To facilitate this, it’s helpful to distil the effect down into a single metric.

In this case, we’re interested in the long return on Friday and the short return on Monday. So I create a metric that is simply the log return if the day is Friday, the negative of the log return if it’s Monday, and zero otherwise.

The metric is in column M and looks like this (you can see its formula in the formula bar):

How consistent is the effect over time?

We can then make another pivot table to explore the consistency of the effect in each year.

Again, we do Insert –> Pivot Table. This time, we put year on the x-axis and the sum of our metric on the y-axis. We can make a bar chart just like we did last time:

This is quite interesting. The effect has existed in all but four years in our data set, and is frequently quite significant. The bar chart looks decidedly non-random.

A time series plot of the effect

I like to look at these effects from a few different angles.

As I work through my research process, I’m essentially on a mission to poke and prod, to uncover whatever is lurking in the data. I’m not goal-focused (that is, I’m not trying to build a trading strategy). I’m simply trying to understand the effect as best I can.

If I do a good job, then the trading strategy should be obvious.

To this end, I like to create a time-series plot of the metric that captures the effect. The most effective way to do this is to plot the cumulative returns to trading the effect without costs or other considerations.

Create another pivot table, and this time, put the date on the x-axis and the cumulative sum of the metric on the y-axis.

To do this, you need to click on the metric variable in the values box and select “Value field settings”. Then, go to the “Show values as” tab, select “Running Total In”, and make sure date is in the “Base field” box:

Then, create a time-series plot by selecting a cell in the numerical output and selecting Insert –> Line cart.

Here’s the result:

This looks a bit like a backtest, but it isn’t.

A backtest, at least to my way of thinking, is a simulation of the trading rules that you come up with to harness the effect and includes costs and other relevant assumptions. In most cases, it’s path-dependent and subject to luck, but is useful in showing you whether you could have harnessed the effect in the past.

What we’re doing here is something else.

It’s simply the cumulative sum of the returns to trading the effect perfectly and without any costs. It gives us a sense of the variability of the effect over time.

The plot tells me that the effect has been surprisingly consistent and, again, looks decidedly non-random.

Do we have a trade?

We have good evidence in the data, but we lack a plausible hypothesis for the existence of this effect.

In practical terms, the lack of a plausible hypothesis means that there’s a decent chance that we’re looking at an artefact of randomness that is just as likely to disappear tomorrow as it is to continue.

But what we see in the data looks non-random, so we can’t discount it entirely.

Personally, I would consider trading this to the long side on Fridays (that is, get long on Thursday’s close and flatten at Friday’s close), but I wouldn’t bother playing the short side on Monday (the historical return is small relative to costs). I’d do it small, which is an acknowledgement that I don’t have any basis for the trade other than what I see in the data, and therefore, this is more likely to be a random effect than most of the other things I trade.

I’d also do a backtest in the face of realistic costs to see how effectively it could have been traded in the past.

Conclusion

I hope this example demonstrates that Excel can be a powerful and productive tool for data analysis.

Using Excel, we performed a fast and efficient analysis of the day-of-the-week seasonality effect in gold.

We have evidence for a non-random effect in the data but no plausible hypothesis for why the effect should exist. Therefore, we proceed with caution.

When it comes to data analysis for trading, use whatever tools help you understand the market. Sometimes, that’s programming. Sometimes, it’s good old Excel. The market doesn’t care what software you use – it only cares whether you have an edge.

Leave a Comment