How to Use the Stock Chart for Statistical Analysis with Excel - dummies

How to Use the Stock Chart for Statistical Analysis with Excel

By Joseph Schmuller

If you’re keeping tabs on all the companies in your diversified stock portfolio, the Stock chart in Excel is the one for you. The image below shows the prices for Google stock for January 5–13, 2016.

stock chart
A Stock chart showing Google stock prices January 5–13, 2016.

Each data point in the Stock chart is a box with a line extending upward and a line extending downward. The upper and lower bounds of the box represent the opening and closing prices for a particular date — note that this does not say respectively. That’s because sometimes the opening price is higher than the closing price, and sometimes vice versa.

How do you know which is which? If the box is empty, the opening price is the lower bound (and the stock posted a gain for the day). If the box is filled, the opening price is the upper bound (and the stock posted a loss).

The endpoint of the upward-extending line is the high price for the day, and the endpoint of the downward-extending line is the low price for the day.

Notice that Excel fills in the x-axis with empty dates (days when the stock market was closed.)

To create the chart, follow these steps:

  1. Enter your data into a worksheet.
    Check out the data in the worksheet.

    9781119271154-fg0334

  2. Select the data that go into the chart.
    For this example, that’s cells A1 through E8.
  3. Click Insert | Recommended Charts, and select the chart type.
    Curiously, Excel does not recommend the Stock chart. So, you select the All Charts tab, pick Stock in the left column, and choose Open-High-Low-Close from the options. Here is the result.

    excel

  4. Modify the chart.

Click the generic chart title and type a new title. Next, click the Chart Elements tool (labeled with a plus sign) and used it to add generic axis titles to the chart. Then, type new titles to replace the generic ones. Finally, select each axis title and pressed Ctrl+B to turn the font bold. You can do that for the chart title, too.