“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.

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.

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)

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.

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

The Subprime Mortgage Crisis had the most impressive rebounds.

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