Excel Workbook: Day of Month Seasonality


Back to a topic from earlier this month: day of month seasonality.

Using a simple walk-forward test to minimize hindsight bias, I showed that trading the days of the month that have been strong historically, has led to stronger returns in the future since 1950 (read posts one and two).

A number of readers had trouble understanding how to run this type of analysis, so I’ve put together an Excel workbook to demonstrate.


Note that the workbook is about 4 MB. It’s in XLSX format (Office 2007) because an XLS file is just too big for downloading. And I’ve only included data back to 1980 (meaning the walk-forward begins in 2000) because, again, the file would be too big otherwise.

Once you download the file, you can populate the required data and copy all formulas to your new cells.

Cells colored grey designate information required by the user: date (column A), S&P 500 closing prices (B), the day of the month (C) and the total number of days in the month (D), and when the 20-year lookback begins for that date in terms of date (F) and row number (G).

I use columns F and G, rather than just taking the previous 5040 trading days (which would roughly equal 20 years), to compensate for potential earlier data when the market was open 6 days a week.

Columns AY through BR show days that qualified as the best half of days in the walk-forward. And columns BV and BW show hypothetical (frictionless) portfolios that only trade either qualifying or non-qualifying days.

Note that the S&P 500 data provided is not dividend adjusted.

. . . . .

P.S. In a previous post I showed how October would have broken down into quartiles based on day of month seasonality (using a more sophisticated approach than the one in the Excel workbook, but one that’s similar in spirit).

That breakdown has been very accurate so far this month with 5 of 6 qualifying days closing up, versus 2 of 6 non-qualifying days.

I should mention that those kind of numbers are definitely not par for the course, so temper expectations. As I’ve shown previously, this seasonality bias does not by itself justify a trade – only potentially biasing the trade.

Happy Trading,

. . . . .

To stay up to date with what’s happening at the MarketSci Blog, we recommend subscribing to our RSS Feed or Email Feed.

7 Responses to “Excel Workbook: Day of Month Seasonality”

  1. 1 Miles Willcod

    Thanks for sharing.

  2. 2 Mr Rachel M

    day of month?

    i suppose you think women can’t trade, too.

    what a sexist article!

    • 3 MarketSci

      Hmmm…humor that escapes my socially awkward nerd mind, or actual critique from someone who obviously didn’t bother reading the post?

      • 4 implied volatility

        I’d go with the first. Haha. I like “her” (?) title too: Mr. Rachel.

      • 5 vimal

        Thanks for the hard work in putting this together. Much appreciated

  3. 6 klh

    cant find anything remotely related to sex or sexism here – what a disappointment:)

  1. 1 Wednesday links: easy targets for a lack of success | Abnormal Returns

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s