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.

How to Survive a Bear Market Attack
Financial Planning
How to Survive and Thrive from a Bear Market Attack

Imagine you’re hiking in the Alaskan wilderness with a friend, and not far away, you spot two little cubs heading toward you both. Your heart starts pounding, wondering where momma bear is until you get startled by the sight of an angry grizzly ready to pounce the two of you …

War is Stupid, So is Panic Selling

Russia has invaded Ukraine, another black swan event to wreak havoc in the stock market just when the world has barely recovered from the effects of COVID-19. Putin must have lost his mind— a scary proposition considering that Russia has the most nukes in its arsenal. What’s next? The Chinese …

Calculating Warren Buffett's Rate of Return
Calculating Warren Buffett’s Rate of Return (and what you can learn from it)

In a 2018 Berkshire Hathaway annual shareholder meeting, Warren Buffett stated that if someone invested $10,000 in a stock index fund back in 1942, it would have been worth $51 million. An index fund is a type of mutual fund designed to match the performance of the overall market. For …

%d bloggers like this: