Visualizing the Best and Worst Days of the Dow

Visualizing Worst and Best Days of the Dow

“Dow falls 832 points in the third-worst day by points ever” was the headline last Thursday on CNN Business. The media loves to sensationalize declines in the stock market, like any other news topic. The increase in readership almost always results in increases in advertising revenue. But the truth is the decline is not even in the top 30 market drops in the Dow’s recent history relative to the size of the market.

This post talks about the process of how to determine the worst and the best days and what we can learn from it. I went through the exercise because I was curious to know how the 832 points drop compares to other declines. Many blog posts tell you what they are, but none disclose how they came up with the information.

Today is your lucky day. By reading this post, you’ll know how you can create similar visualizations for any market index or a stock ticker, for that matter. A Google account and basic spreadsheet skills are the only prerequisites.

How to Determine the Worst and the Best Days

Step 1. Download historical prices from Yahoo Finance

Many financial websites let you download historical data, but only a few let you download the same for free. Yahoo Finance is one of them, and you don’t even need to create an account. You get historical prices in CSV (comma separated values) format.

Yahoo Download DJI Data

Step 2. Import CSV into Google Sheet

Follow the following steps from within Google Drive…

  1. Click the upload button (to the right of “Create”), and choose “Files”
  2. Pick the .csv file that you downloaded in Step 2 from your computer.
  3. If you go straight to the upload box, then click “Settings”, make sure “Convert uploaded files to Google Docs format” is checked.
  4. Upload your CSV. It should be converted into a spreadsheet.

Step 3. Add a “% Return” Column

The “percent return” column tells you the closing gain or loss relative to the opening price. To calculate this for the first data row, you’ll need the difference between the E2 and B2 column values divided by B2.

Add "% Return" Column

Enter the formulas “=((E2-B2)/B2)” and “=((E3-B3)/B3)” for the first and second rows of the column, respectively. Follow the instructions below to fill up the remaining rows.

  1. Click the first cell and then the second while holding the shift key to select both cells.
  2. Hover to the lower right corner of the second cell. The cursor should change from an arrow to a cross (+).
  3. Click and drag the selection way to the very end of the result set. The values for all cells should be automatically calculated.

Finally, you want to make the column values readable by changing the format to a percentage. To do this, click the spreadsheet column header H then click on the “Format as percent” icon on the toolbar.

Step 4: Sort Accordingly

Now that the Return column is available, let’s hide the data that we no longer need. Click column header B and then G while holding the shift key, to select columns B to G, then right-click and pick the “Hide columns B-G” menu item. Only columns A and H should be visible.

Follow the instructions below to sort the resultset.

  1. Select all data by clicking Control-A
  2. Select “Data” from the main menu and then click “Sort range…” from the context menu
  3. Check “Data has header row” option
  4. Pick sort by “Return” option
  5. Pick the desired sorting order (“A->Z” for Worst Days or “Z->A” for Best Days)
How to sort

Step 5: Generate Chart

Follow the instructions below to generate the chart.

  1. Select the cells that you want to visualize (e.g. rows 1 to 11, for the Worst 10 days) including the headers.
  2. Select “Insert” from the main menu and then click “Chart”. The default chart with non-sensical data should show up.
  3. Ensure that the chart type is set to “Column chart”.
  4. In the Chart Editor’s data tab, ensure that the X-axis is set to “Date” and Series is set to “Return”.

Sample Visualizations

The following are sample visualizations that you can create using the steps demonstrated above.

Worst Days of the Dow

Last Thursday’s decline is not even in the top 30 market drops in the Dow’s recent history

Best Days of the Dow

The Subprime Mortgage Crisis had the most impressive rebounds.

Worst and Best Days of the Dow

Market declines (red) are almost always immediately followed by rebounds (green). You’ll likely regret selling!

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Why You Absolutely Don’t Want to Miss the Best Days

The past 90 days were particularly exciting if you’re a stock market investor. It reminds me of the Hershey Park roller coaster I was compelled to ride to accompany my 10-year-old nephew. It was a terrifying experience, but not for my nephew, who was having a blast the entire time, …

Power of compounding continues to amaze me
The Power of Compounding Continues to Amaze Me

Let’s say you’ve saved Warren Buffett’s cat from falling into a ditch. Extremely grateful, he offered a reward that lets you pick between two options: Which option would you pick? Option 1 or Option 2? If you picked the first option, congratulations, you’re $3,000,000 richer: But then, you just short-changed …

Why you probably don't want to trade options
Why You Probably Don’t Want to Trade Options

When the market is moving haywire, many DIY investors are tempted to be more creative with their investments. So, they resort to strategies that will likely ruin their portfolios: day trading, buying on margin, short selling, etc., instead of sticking to the proven, slow-and-steady, or buy-and-hold approach to investing. One such strategy …