6. Creating Stock Analysis Charts

Overview of Stock Charts

Excel provides four basic types of stock analysis charts: High-Low-Close, Open-High-Low-Close, Volume-High-Low-Close, and Volume-Open-High-Low-Close. These built-in charts are helpful when you need to display a stock trend for use in an executive dashboard.

Unfortunately, like the old charting in legacy versions of Excel, the stock charts are showing signs of age. If you are familiar with the charts in the Wall Street Journal or on http://finance.yahoo.com, you can see that charting technology has definitely left Excel behind.

This chapter guides you on how to coax acceptable results out of the Excel charting engine. However, sometimes it is easier to ditch the Excel stock charts and design your own chart using a line chart.

If you are planning to do dashboard reporting, this chapter provides a few tips to help make your charts smaller than usual, while maintaining readability.

Typically, stock charts in the newspaper or online are represented by one of three chart types: line charts, open-high-low-close (OHLC) charts, or candlestick charts. The following sections discuss each of these chart types.

Line Charts

A line chart shows the closing price of a security every day for a month, quarter, half year, year, or longer. A line chart may show a second series of volume represented as a column chart at the bottom of the chart. For example, in Figure 6.1 a line chart shows the closing price for a security for one year. A volume chart at the bottom shows unusually high activity for the security in February and October. When creating a line chart in Excel, you do not have to use stock chart types. Instead, you can simply choose a line chart.

One advantage of line charts is that it is easy to add a second security to a line chart to show how the original security is doing compared to an index or a competitor.

Figure 6.1 A line indicates the closing price of the security each day for a year. The column chart at the bottom shows the volume of shares traded each day.

OHLC Charts

An OHLC chart shows a vertical line extending from the low price to the high price for a given period. A dash on the left side of the line indicates the opening price. A dash on the right side of the line indicates the closing price. For example, the chart in Figure 6.2 shows that January opened at 199, the price ranged from 126 to 200, and January closed at 135.

Figure 6.2 This is a true OHLC chart. Excel’s built-in types omit the marker for the opening price.

Excel does not have a built-in style for OHLC charts, but it can create a variant of this chart. Excel’s high-low-close chart shows the vertical line and the closing line on the right side of the line. However, it is missing the marker for the opening price. Excel’s volume-high-low-close chart is a variant of the OHLC chart that is coupled with a volume chart showing trading volume.

→ If you desperately need to show the opening price, see the “Creating OHLC Charts” section later in this chapter.

Candlestick Charts

A candlestick chart has a vertical line that indicates the range of low to high prices for a security. A thicker column indicates the opening and closing prices. If the price of the security closed up, the thicker column appears in white or green. If the price of the security closed down, the thicker column appears in black or red.

This stock chart was named a candlestick chart because each shape appears as a candle with a wick sticking out of the top and the bottom. For example, in Figure 6.3, the security declined in the five months from July through November, before gaining in December.

Excel creates candlestick charts using the Open-High-Low-Close chart type. In another variant, volumes for each period are plotted on a second axis. You can quickly scan this type of chart to see whether the stock has had more winning periods than losing periods.

Figure 6.3 The thicker column indicates the open and closing prices. The thinner line indicates the high-to-low range.

Obtaining Stock Data to Chart

There are plenty of free sources of historical data to chart. To obtain stock data to chart, you can go to http://finance.yahoo.com or another such site and follow these steps:

1. If you do not know the stock ticker symbols for the company of interest, use the Symbol Lookup link that appears next to the Go button in the top navigation bar of the page.

2. Enter a stock ticker symbol in the Get Quotes text box and press the Go button. Yahoo returns a table and a chart showing information about the current day.

3. Click Historical Prices in the left navigation bar. Enter a starting date and an ending date and choose whether you want the data summarized daily, weekly, or monthly. Click Get Prices to generate new results. Yahoo shows columns for date, open, high, low, close, volume, and adjusted close. A minor annoyance is that it shows about 50 dates on a page and then offers a Next link.

4. Instead of copying a page at a time, scroll down and select the Download to Spreadsheet link that appears below the results.

5. In the File Download dialog that appears, click Save. An imaginative name of table.csv is proposed. Save using this name of something, such as MSFTDaily2008.csv.

6. In Excel 2010, select File, Open. In the Open dialog that appears, in the Files of Type drop-down, select Text Files (*.prn, *.txt, *.csv).

7. Browse to the downloaded .csv file and click Open. Excel opens the file. Column A, which contains dates, is typically too narrow, as shown in Figure 6.4. Double-click the border between the Column A and B column headings to make Column A wider.

Figure 6.4 After the CSV file opens in Excel, you need to adjust the column widths.

8. The data is always sorted with the most recent data first. Therefore, click a cell in Column A and select Data, AZ to sort the date into ascending sequence by column.

9. If you have more than one screen of data, from the View tab, select Freeze Panes, Freeze Top Row to ensure that you can always see the headings at the top of the screen.

10. CSV files are not good places to store Excel charts. Before creating any charts, select File, Save As. Select to save as an Excel 2010 macro-enabled workbook.

Rearranging Columns in the Downloaded Data

If you are using one of the Excel built-in stock charts, you should know that Excel is very particular about the sequence of the columns. For example, in a high-low-close chart, the date should be in the first column, followed by a High column, a Low column, and a Close column. This does not match the sequence of the data downloaded from Yahoo.com. You need to be prepared to insert new columns, and then cut and paste data from one column to another in order to sequence your data as necessary. The following list shows the required sequence of columns for each chart type:

• Line Chart—Date in Column A and Close in Column B

• Line Chart with Volume—Date in Column A, Close in Column B, and Volume in Column C

• High-Low-Close—Date in Column A, High in Column B, Low in Column C, and Close in Column D

• Volume-High-Low-Close—Date in Column A, Volume in Column B, High in Column C, Low in Column D, and Close in Column E

• Open-High-Low-Close as Candlestick—Date in Column A, Open in Column B, High in Column C, Low in Column D, and Close in Column E

• Open-High-Low-Close as OHLC—Date in Column A, High in Column B, Low in Column C, Close in Column D, and Open in Column E

• Volume-Open-High-Low-Close as Candlestick—Date in Column A, Volume in Column B, Open in Column C, High in Column D, Low in Column E, and Close in Column F

• Volume-Open-High-Low-Close as OHLC—Date in Column A, Volume in Column B, High in Column C, Low in Column D, Close in Column E, and Open in Column F

Dealing with Splits Using the Adjusted Close Column

Before charting data, you should look at the earliest data point and compare the Close column to the Adjusted Close column. If they differ, you know that one of two events happened during the period in question:

• The company declared a dividend. For example, if the company pays out three cents per share, the adjusted price is reduced by three cents for all months that occurred earlier than the dividend.

• If the company declares a stock split, the adjusted close shows the closing price, which pretends the split had occurred previously.

Figure 6.5 shows an example of a stock split. Say that Activision stock began September 2008 at a price of $33.66 and closed the month at $15.43. The stock did not really incur a huge drop during the month.

Activision had declared a two-for-one stock split on September 8, 2008. Every person who had 100 shares on that day watched those shares change into 200 shares. The value of each share was cut in half at the time of the split. In this case, if you start the month with 100 shares of Activision, valued at $3,366, you will end the month with 200 shares of Activision, valued at $3,086. This is a drop, but not a massive 50 percent drop.

Figure 6.5 At first glance, it appears the Activision stock took a nosedive in September.

To learn when the split or dividend occurred, you have to look through the table on http://finance.yahoo.com, which shows splits and dividends. This information is not downloaded in the CSV file.

If you are plotting a line chart showing the closing price, you can deal with the split by using the Adjusted Close column. Notice in Figure 6.5 that the Adjusted Close column for January 2008 is $$12.94, half the real closing price of $25.87. Yahoo goes to the trouble of adjusting the closing price to provide a comparable view of the closing price.

If you are plotting a chart showing high, low, and close, you have to add some additional calculations. To do so, follow these steps:

1. Add the new column headings Date, High, Low, Close to H1:K1.

2. Copy the formula =A2 from cell H2 down to all rows. This formula is for the date.

3. Copy the formula =G2 from cell K2 down to all rows. This formula is for the adjusted close.

4. Copy the formula =C2*($G2/$E2) from cell I2 down to all rows.

5. Manually fix any dates where a split occurred.

6. Create your stock charts from the data in Columns H:K, as shown in Figure 6.6.

Figure 6.6 Most of the adjusted columns are a formula, but you need to use special care in the months in which a stock split occurred.

Creating a Line Chart to Show Closing Prices

A line chart is the easiest type of stock chart to create. Instead of using Excel’s built-in stock charting types, you will use a line chart. Follow these steps to create a line chart:

1. Download data for the security from http://finance.yahoo.com.

2. Sort the data into ascending sequence by date.

3. Insert a blank Column B after the Date column.

4. Copy the Adjusted Close column from Column H to the new column B.

5. Delete the extra Column H.

6. Clear cell A1.

7. Replace the Adjusted Close heading in B1 with the security symbol and time period such as MSFT 2008.

8. Select your data in Columns A and B.

9. On the Insert tab, select Line, 2-D Line, Line. Excel creates the chart shown in Figure 6.7.

Figure 6.7 Excel creates a line chart showing closing prices.

10. Click the legend and press the Delete key. Excel removes the legend from the chart.

11. The value axis currently runs from a low of 0 to a high of 35. During 2008, the security closing prices ran from a low of $17.50 to a high of $35.96.

12. The dates in the horizontal axis are trying to show month, day, and year, as in the original dataset. To display one label for each month, do the following:

• Assuming the Format dialog box is still open, reach behind the dialog with the mouse and click the horizontal axis.

• On the Axis Options dialog, select Major Unit, Fixed, 1, Month.

• Select Axis Type, Date Axis.

• Click the Number category in the left navigation bar.

• Click the Date category.

• Scroll near the bottom of the Type list box and select M. The mmmmm custom type displays a single letter for each month. In the English version of Excel, it displays JFMAMJJASOND, a format regularly seen in the Wall Street Journal.

13. Resize the chart so that it is narrower than the default chart. Click the chart border to select the chart. Drag the right resizing handle to the left.

14. If the chart is in Draft mode, open the Draft Mode drop-down and select Turn Off Draft Mode.

The resulting chart is shown in Figure 6.8.

Figure 6.8 When you zoom in on the $20–$30 price range, more details are visible.

Adding Volume as a Column Chart to the Line Chart

A popular option in stock charts is to add a column chart that shows volume of shares traded. This chart usually appears at the bottom of the trend chart.

Continuing with the example from the preceding section, to plot prices in the $15–$35 range with volumes in the 15–300 million range, the volumes have to be plotted on a secondary axis. One trick is to inflate the maximum artificially for the secondary axis by a factor of three or four in order to keep the volume chart in the lower portion, which is the lower quarter to third of the chart.

Follow these steps to create a chart that shows closing prices and volume:

1. Download data from http://finance.yahoo.com.

2. Sort the data into ascending sequence by date.

3. Insert blank Columns B and C after the Date column.

4. Cut the Adjusted Close column from Column I to the new Column B.

5. Cut the Volume column from Column H to the new Column C.

6. Clear the heading from cell A1.

7. Select your data in Columns A:C.

8. Select Line, 2-D Line, Line from the Insert tab. Excel creates the chart shown in Figure 6.9. Initially, you will only see the line for Volume. The Closing price will appear as a flat line at zero. You will fix this in the following steps.

9. Click the Legend and press the Delete key. Excel removes the legend from the chart.

10. Double-click the Volume series on the chart. Excel displays the Format Series dialog.

Figure 6.9 Do not be alarmed that you can see only volumes.

11. Select Secondary Axis. Click OK to close the dialog.

12. While the Volume series is still select, select Design, Change Chart Type. Select the first column chart type. Click OK.

13. On the Layout tab, select Chart Title, Centered Overlay. Type MSFT 2008 and press Enter to change the title.

14. The value axis currently runs from a low of $0 to a high of $35. Although prices of Microsoft (MSFT) stock never dipped below $15 in 2008, you can leave that space to hold the volume portion of the chart. The highest volume was about 300 million shares traded. If you scale the secondary axis to have a maximum value of 600 million shares traded, the volume portion of the chart occupies the lower half of the chart. Right-click the secondary value axis, and then select Format Axis. Change Maximum to Fixed, 6E8 (which is 600,000,000). The tallest column in the volume area of the chart stays below the gridline for $20. Keep the dialog box open for steps 15 and 16.

15. Usually, the analyst does not care how many shares are traded; he or she is interested in the relative scale of the shares being traded. From the chart, you can tell that something remarkable happened when Microsoft traded four times more shares than usual. Thus, you do not need to have any volume numbers along the right side of the chart. Change the Axis Labels drop-down to None.

16. The dates in the horizontal axis are trying to show month, day, and year, as in the original dataset. To display one label for each month, do the following:

• With the Format dialog open, reach behind the dialog and click the dates along the horizontal axis.

• On the Axis Options dialog, select Major Unit, Fixed, 1, Month.

• Select Axis Type, Date Axis.

• Click the Number category in the left navigation bar.

• Click the Date category and select M from near the bottom of the Type list box. This type displays only the first letter of each month name. Therefore, it appears as JFMAMJJASOND.

17. Resize the chart so that it is narrower than the default chart. Click the chart border to select the chart. Drag the right resizing handle to the left.

18. If the Draft mode indicator appears on the chart, open the Draft mode indicator and select Turn Off Draft Mode.

Figure 6.10 shows the resulting chart.

The process of creating line charts is quite straightforward. Although a certain amount of tweaking needs to happen, it is about normal for a chart. In contrast, when creating OHLC charts, you must jump through more hoops, as described in the next section.

Figure 6.10 The final chart shows closing price as a line chart and volumes as columns at the bottom of the chart.

Creating OHLC Charts

Excel offers two built-in chart types that come close to the OHLC chart shown earlier in Figure 6.2. The built-in types both ignore the left-facing dash used to indicate the opening price each day.

Microsoft is not being dense here. Instead, a fundamental flaw exists in the underlying chart engine that makes it difficult to show the left-facing marker. This is why Microsoft does not support the open marker in the built-in charts. As you will see in the sections that follow, you can work around this flaw.

Producing a High-Low-Close Chart

Before progressing to a true OHLC chart, it is best to start with Excel’s built-in high-low-close chart. Follow these steps to produce a high-low-close chart in Excel 2010:

1. Download data from http://finance.yahoo.com. Because you cannot save a chart in a CSV file, use the Save As command to save the file as a regular Excel file type.
2. Move the Open data from Column B to the blank Column H. Delete the now-empty Column B. This leaves you with dates in Column A, High in Column B, Low in Column C, and Close in Column D.
3. Select your data in Columns A:D.
4. On the Insert tab, select the Other Charts icon. In the Other Charts menu, the first four thumbnails are the four built-in stock charts (see Figure 6.11). Select the first stock icon (High-Low-Close). Excel creates the default chart shown in Figure 6.12.

Figure 6.11 The four built-in stock charts are hidden under the Other Charts icon.

Figure 6.12 The default stock chart has formatting problems that make it difficult to see the close icons.

The default chart leaves a lot to be desired. For example, when looking at the vertical line extending from low to high, notice that it is nearly impossible to see the marker for the close. In addition, the legend on the right side does not add useful information to the chart.

If you turn to the Chart Layouts gallery on the Design tab, you face a perplexing selection. Layouts 1 and 3 appear to be identical. Layout 2 attempts to add data points for high, low, and close, making it impossible to see anything. The gray plot area in Layout 5 is not actually a gray plot area. Instead, it is Microsoft’s attempt to draw 100 gridlines. The only interesting layout is Layout 4, in which Excel adds a data table. Layout 4 works for the charts in Figure 6.13 because they have only 12 months.

If you dare to choose any of the layouts from the Chart Styles gallery, the close markers change from being imperceptibly small to being far too large. Because Close is the third series, the markers are automatically upward-pointing triangles. This leads to the mistaken impression that the stock was trending up at the time the market closed (see Figure 6.14).

Figure 6.13 None of the built-in layouts improves the stock chart.

Figure 6.14 If you try to assign a style from the Design tab, you will automatically get triangles, which are the markers traditionally used for the third series.

Customizing a High-Low-Close Chart

It is possible to make an acceptable high-low-close chart in Excel 2010. After you delete extraneous chart elements and zoom in, you need to format and change the marker style for the Close series. Here’s how you do it:

1. Click the Legend and press the Delete key to remove the legend from the chart.
2. Double-click the Vertical Axis to display the Format Axis dialog box.
3. In the Axis Options category in the Format Axis dialog box, select Minimum, Fixed. Enter a number that is a bit lower than the low value in the chart. In the current example, a low value of 75 is appropriate. Keep the Format dialog box open for steps 4 through 12.
4. On the Layout tab, open the Current Selection drop-down and select Series “Close”.
5. Select the Marker Options category along the left side of the dialog.
6. Increase the Size setting to 9 to ensure that the markers are visible.
7. Click the Marker Fill category in the Format Data Series dialog box. Select Solid Fill, and then select the black color.
8. Click the Marker Line Color category in the Format Data Series dialog box. Select Solid Line and then select the black color.
9. Click the horizontal gridlines in the chart.
10. For Line Color, select Solid Line. From the Color drop-down, select gray.
11. Click the Line Style category in the left of the dialog box.
12. Change the width to 0.5 points. Click Close to dismiss the Format dialog box.
13. Select Layout, Chart Title, Center Overlay Title. Type a title of AAPL 2008 and then press Enter.
14. Click the chart border. Drag the right resizing handle to the left to shrink the chart.

Figure 6.15 shows the resulting chart.

Figure 6.15 After formatting the high-low-close chart, you can actually see the close markers.

Creating an OHLC Chart

The fundamental barrier to creating a true OHLC chart is that Excel does not offer a left-facing dash as a built-in marker for a chart. However, you can import your own image to use as a marker.

Alternatively, you can use Photoshop to create a new graphic. For example, I created a graphic that was 11 pixels wide and 3 pixels tall. The leftmost five columns of pixels in this graphic are black, and the remaining pixels are transparent. I saved this file as a GIF image named LeftDash.gif. However, if you do not want to go to take the time to create an image like this, you can download this graphics from the web page of examples for this book at http://www.MrExcel.com/chart2010data.html.

The trick to creating an OHLC chart is to start with a high-low-close chart and add the Open series with a custom marker style. Follow these steps to create an OHLC chart in Excel 2010:

1. Start with data that has Date in Column A, High in Column B, Low in Column C, Close in Column D, and Open in Column E. Do not include the Open data in the initial selection. Select the data in A:D.
2. From the Insert tab, select Other Charts, Stock, High-Low-Close. Excel draws a chart. A blue box surrounds the charted data in B2:D13.
3. Click the blue handle in cell D13. Drag to the right to include the Open data on the chart. Excel adds the Open data in a format similar to the Close data.
4. On the Layout tab, select Series Close from the Current Selection drop-down. Click Format Selection.
5. For the Marker Options category, leave the marker as the right-facing dash and change the size to 7.
6. For the Marker Fill category, select Solid Fill. Select black from the color drop-down.
7. For the Marker Line Color category, select Solid Line. Select black from the color drop-down.
8. Without closing the Format dialog box, select Series Open from the Current Selection drop-down on the Layout tab. Once again, select the Marker Options category.
9. Change the Marker Type setting from None to Built-in.
10. In the Type drop-down, select the tenth marker, which is a tiny version of the Picture icon that is prevalent throughout Excel.
11. Click the Marker Fill category in the left navigation bar of the Format Data Series dialog.
12. Select Picture or Texture Fill. Excel updates the chart to show the default brown paper texture. Do not worry how this looks now since you will fix this later.
13. Click Insert from File in the dialog. Navigate to and select LeftDash.gif. Excel automatically adds a line around your marker. Even though the right side of the marker is transparent, Excel outlines the entire marker.
14. For the Marker Line Color category, select No Line. Click Close to close the Format dialog box.
15. On the Layout tab, select Legend, No Legend.
16. Select Chart Title, Above Chart. Type the title AAPL 2006.
17. Resize the chart so that it is horizontally smaller.
18. Right-click the numbers along the vertical axis, select Format Axis, and then select Minimum, Fixed, 50.

Figure 6.16 shows the final chart.

Figure 6.16 The markers for the Open series are image files created in Photoshop.

The process of adding the open markers adds complexity to creating this chart. However, if you frequently need to create OHLC charts, you can save this chart type as a template to streamline the process in the future. To save a chart as a template, select the chart, and then select Save As Template from the Type group on the Design tab.

Adding Volume to a High-Low-Close Chart

There are two ways to add a volume column chart to a high-low-close chart:

• Microsoft offers a built-in volume-high-low-close chart. However, this built-in chart automatically moves the prices from the left axis to the right axis.

• You can add volumes while keeping the prices along the left axis.

Creating a Built-in Volume-High-Low-Close Chart

Follow these steps to create a built-in volume-high-low-close chart:

1. Arrange your data with Date in Column A, Volume in Column B, High in Column C, Low in Column D, and Close in Column E.

2. If you have actual dates in Column A, remove the Date heading from the top-left corner cell.

3. Select the range of data in A:E.

4. On the Insert tab, select Other Charts, Volume-High-Low-Close. Excel creates the chart shown in Figure 6.17.

Figure 6.17 The volume columns hide the high-low-close markers for most of this chart.

5. Click the Legend and then press the Delete key.

6. Double-click the numbers along the left side of the chart.

7. When the Format Axis dialog appears, click Maximum Fixed and triple the value shown in the box. In this example, you triple the original value of 8.0E7 to 2.4E8.

8. To remove the axis labels for the Volume columns, change the Axis Labels drop-down in the center of the Format Axis dialog to None. Change the Major Tick Mark Type drop-down to None.

9. While keeping the Format dialog open, click the Layout tab and open the Current Selection drop-down. Select Series Close.

10. Click Marker Options in the left navigation bar of the Format Data Series dialog. Increase the size from 5 to 9.

11. The gridlines shown in the chart are for the volume columns. To remove the gridlines and insert gridlines for the prices, on the Layout tab, select Gridlines, Primary Horizontal Gridlines, None. Then select Gridlines, Secondary Horizontal Gridlines, Major Gridlines.

12. From the Current Selection drop-down, select Secondary Vertical (Value) Axis Major Gridlines. The Format dialog box changes to Format Major Gridlines.

13. In the Format Major Gridline dialog, select Solid Line. In the Color drop-down, choose a light gray color to make the gridlines less obtrusive. Click Close to close the dialog box.

14. On the Layout tab, select Chart Title, Centered Overlay Title. Type AAPL 2008 and press Enter.

15. Click the border of the chart to select the chart area. On the Format tab, select Shape Outline, No Outline to remove the extra box around the chart.

16. Reduce the horizontal size of the chart by clicking the right resizing handle and dragging toward the center of the chart.

Figure 6.18 shows the resulting chart. However, it can be a bit disconcerting to have the axis scale appear on the right side of the chart when 99 percent of the charts in the Western world have the axis appear on the left side of the chart. A solution for this problem is provided in the next section.

Figure 6.18 After a number of adjustments, the built-in volume-high-low-close chart does the job, although the axis appears on the wrong side.

Adding Volume to the Right Axis of a High-Low-Close Chart

Although the method described in this section is a bit more complicated than the method described in the preceding section, it enables you to add the volume to the axis on the right side of the chart. This method abandons the built-in stock chart types and reveals that the stock chart types are really just an interesting mixture of standard settings.

The following steps add volume to the high-low-close chart shown earlier in Figure 6.15. You can expand the concept to add an Open marker as in the chart shown in Figure 6.16.

Follow these steps to create a volume-high-low-close chart:

1. Download data from http://finance.yahoo.com. Rearrange the data to show Date in Column A, High in Column B, Low in Column C, Close in Column D, and Volume in Column E.

2. Remove the Date heading from the top-left cell in the range.

3. Although you have data in Columns A:E, select only the data in Columns A:D.

4. On the Insert tab, select Line, 2-D Line, Line. Excel creates the chart shown in Figure 6.19.

5. On the Layout tab, select Series High from the Current Selection drop-down. Click Format Selection and then select Line Color, No Line.

Figure 6.19 This line chart appears to be a long way from the OHLC format you desire.

6. Without closing the Format Data Series dialog, select Series Low from the Current Selection drop-down in the Layout tab. In the Format Data Series dialog, select Line Color, No Line. The high and low lines are now invisible, as shown in Figure 6.20.

Figure 6.20 Make the high and low lines invisible.

7. Keep the Format dialog open. From the Layout tab, select Lines, High-Low Lines. Excel draws vertical lines between the invisible high and low points, as shown in Figure 6.21.

Figure 6.21 Add High-Low lines to draw the vertical lines from the invisible high and low markers.

8. Keep the Format dialog open. Select Series “Close” from the Current Selection drop-down on the Layout tab. In the Format dialog, make the following selections:

• Select Marker Options, Built-In, Type, select the sixth type, which is a right-facing dash.

• Change the Size setting from 5 to 8.

• Select Marker Fill, Solid Fill, and then select black.

• Select Line Color, No Line.

• Select Marker Line Color, Solid Line, and then select black.

9. Click Close to close the dialog box.You have created a high-low-close style chart from the line chart, as shown in Figure 6.22.

Figure 6.22 The line chart is now a high-low-close style chart. Notice the blue resizing handle in the upper-right corner of D2.

10. Grab the blue resizing handle in the upper-right corner of D2. Drag to the right to add Column E to the chart. Do not be concerned that you no longer see the high-low-close markers. You will get them back before the end of the process.

11. From the Current Selection drop-down on the Layout tab, select Series Volume. Click Format Selection. Select Secondary Axis. Click Close. In a bizarre twist, the columns that appeared in step 9 turn into an invisible line chart.

12. On the Design tab, select Change Chart Type. Select the first column chart icon–Clustered Column. The columns reappear.

13. Double-click the numbers along the right axis of the chart.

14. Click Maximum, Fixed. Double the number in the Fixed text box. Select Major Tick Mark Type, None. Select Axis Labels, None.

15. Click the Legend and press the Delete key.

16. Click the labels along the horizontal axis. Select Axis Options, Major Unit, Fixed, 1, Months. For the Number category, select the date type of M. Click Close.

17. Select Layout, Chart Title, Above Chart. Type the chart title AAPL 2006.

18. Reduce the horizontal size of the chart.

Figure 6.23 shows the final chart.

Figure 6.23 This volume-high-low-close chart was created from a line chart.

The next section describes candlestick charts, which require the least customization because Excel includes good built-in charts to create candlestick charts.

Creating Candlestick Charts

A basic candlestick chart requires a data range that includes a date in the first column, and open, high, low, and close values in the remaining columns. To create a candlestick chart, follow these steps:

1. Download data from http://finance.yahoo.com. Your data will be in the correct sequence, with Date in Column A, Open in Column B, High in Column C, Low in Column D, and Close in Column E.

2. Select your data in Columns A:E.

3. On the Insert tab, select Other Charts, Stock, Open-High-Low-Close. Excel creates the chart in Figure 6.24.

Figure 6.24 The chart shows the default chart created in step 3.

4. Click the legend and press Delete.

5. Double-click the numbers along the vertical axis. Specify a fixed minimum value that is greater than zero but lower than the low value for the range in question.

6. Click the Number section along the left navigation. Select the Date category and then the M type. Click Close to close the dialog box.

7. Select Layout, Chart Title, Above Chart. Type the title GOOG 2008, and then press Enter.

8. Reduce the horizontal size of the chart.

The final chart is shown as the bottom chart in Figure 6.25.

Figure 6.25 After minimal formatting, you have an acceptable chart.

Changing Colors in a Candlestick Chart

By default, Excel makes the candlestick charts monochrome. Stock price increases are shown with white columns. Stock price declines are shown with black columns. If you will be presenting the chart in color, you might prefer another system, such as red for declines and green for increases. If this is the case, it is easy to customize the colors in a chart.

Before adjusting the colors, apply any effects to the chart. For example, open the Chart Styles gallery on the Design tab and choose Style 28 to apply a beveled effect to the up/down bars.

The white up bars and the black down bars are actually two separate objects in the chart. Therefore, first you need to format the up bars and then format the down bars. To change the color of the bars, follow these steps:

1. On the Format tab, select Current Selection, Up Bars 1.
2. Select Format, Shape Fill, and then select green. (In color stock charts, up periods are typically shown in green.)
3. On the Format tab, select Current Selection, Down Bars 1.
4. Select Format, Shape Fill and then select red. (In color stock charts, up periods are typically shown in red.)

Adding Volume to a Candlestick Chart

Excel offers a built-in chart you can use to create a candlestick chart that includes volume bars. However, as with the volume-high-low-close chart, the height of the volume bars is often too large.

Follow these steps to create a chart based on the built-in volume-open-high-low-close chart type:

1. Download data from http://finance.yahoo.com. Save the data as an Excel workbook. Insert a new Column B before the Open column. Move the Volume data from Column G to the new Column B. Delete Column G. Your data will be in the correct sequence, with Date in Column A, Volume in Column B, Open in Column C, High in Column D, Low in Column E, and Close in Column F.
2. Remove the Date heading from cell A1.
3. Select your data in Columns A:F.
4. On the Insert tab, select Other Charts, Stock, Volume-Open-High-Low-Close. This is the fourth stock chart thumbnail. Excel creates the top chart in Figure 6.26.
5. Right-click the numbers along the left vertical axis and then select Format Axis. Specify a fixed maximum value that is about double the original amount. Select Major Tick Mark Type, None. Select Axis Labels, None.
6. On the Layout tab, select Legend, None.
7. Select Chart Title, Above Chart. Type the title GOOG 2006 and press Enter.
8. Right-click the labels along the horizontal axis. Select Format Axis. On the Number tab, specify the custom formatting code mmmmm.
9. Reduce the horizontal size of the chart.

The final chart is shown as the bottom chart in Figure 6.26.

Figure 6.26 The top chart shows the default chart created in step 4. The bottom chart shows the result of the remaining formatting.

Manually Creating a Candlestick Chart with Volume

The problem with the chart in Figure 6.26 is that the stock prices appear on the right side of the chart. If you prefer to have your stock prices on the left side of the chart, you need to abandon the built-in stock charts. However, before you take this step, you need to have a good understanding of how Excel draws in high-low lines and up/down bars. The following section provides explains the rules for high-low lines and up/down bars.

Figure 6.27 shows four line series on a single chart. The first series is the thick solid line from lower left to upper right. The second series is the dotted line at the top of the chart. The third series starts out as the lowest dashed line but crosses to become the second-lowest line late in the chart.

You can add high-low lines to a chart by selecting Layout, Lines, High-Low Lines. Figure 6.28 shows that the vertical lines extend from the lowest value at each data point to the highest value at each data point. In February, the line extends from the 1 in Series 3 up to the 12 in Series 2. In October, the high-low line extends from the 1 in Series 1 to the 12 in Series 2.

In contrast to high-low lines, up/down bars always extend from the first series line to the last series line. In Figure 6.29, the up/down bars always start at the solid line for Series 1 and extend to the dash-dot line for Series 4. It seems like there would be a setting that you could use to specify that the up/down bars should extend from one series to another series.

Figure 6.27 These four series are used to illustrate the different behavior of high-low lines and up/down bars.

Figure 6.28 High-low lines look at all the line series in the chart and extend from the lowest to the highest at each data point.

Instead, Excel always draws the up/down bars from the first series to the last series (see Figure 6.29).

To see a demo of high/low lines, search for MrExcel Charts 6 at YouTube.

Figure 6.29 Up/down bars always start at the first series and extend to the last series.

Creating a Live Chart by Using a Web Connection

In public companies, senior management often spends a lot of time focusing on the current stock price. This might be because they are truly concerned for the individual investors, or it might be that they are interested in the current value of their stock options.

At my employer in the 1980s, an investor relations administrator continually updated a whiteboard showing the stock price of my employer and the two competitors in our industry. Dialing in to a service 24 times a day to update the whiteboard must have been a horrible chore.

Excel offers fabulous tools that automatically queries data from a web page every minute and then refreshes the data in Excel.

To set up a web query, first you need to move the cell pointer to an out-of-the-way location on the worksheet. The web query returns unformatted data that you nearly always want to reformat. Therefore, the query should be located outside the field of view and you should use formulas or charts to display the data. To build this chart, follow these steps:

1. Select cell A40.

2. From the Data tab, select From Web. A New Web Query dialog box appears that shows the home page selected in your installation of Internet Explorer. For example, you might see Google or Bing or Yahoo in the browser.

3. Use the New Web Query dialog box to navigate to your favorite source of stock quote information such as http://finance.yahoo.com. Click in the Get Quotes box, type the ticker symbols MSFT, AAPL, GOOG, and click the Go button. After the web page finishes loading, Excel draws a series of yellow arrows. Each arrow indicates a table on the web page.

4. Scroll down to the table that contains the data you want to import to Excel. While you hover over the yellow arrow, an outline appears that shows the extent of the table. Click the yellow arrow to change it to a green check mark (see Figure 6.35).

Figure 6.35 Select the tables from the web page to be imported.

5. Click the Import button. The New Web Query dialog box is dismissed and the Import Data dialog box appears. Confirm the location for the imported data and then click OK. A strange bit of text appears in the active cell. A few seconds later, a text version of the table appears in your spreadsheet. Keep in mind that this is a one-time snapshot of the data. After you build a few formulas and a chart, you can change the properties of the web query to refresh every minute.

Creating a Bar Chart with Formulas to Extract Imported Data

Next, you need to build formulas that extract the desired information from the imported data. In this case, a simple bar chart with three points will work fine. The formula in I41 builds some text that is used as both the category value and the label for the bar chart. The formula in I41 is =A41&” “&C41&” “&E41. The formula in J41 is more complex, as it has to convert the text value of Down 0.40 percent to a value of -0.4 percent. The formula in J41 is =IF(LEFT(E41,1)=“D”,-1*MID(E41,6,4),1*MID(E41,4,4))/100. Copy these formulas down for the competitor’s stock quotes, as shown in Figure 6.36.

Figure 6.36 Build formulas that you can use to create a chart.

Build a chart using the formulas in I41:J43. Format the chart and move it up to occupy a spot in your executive dashboard. In Figure 6.37, a simple bar chart shows the relative increase/decrease of each security.

Figure 6.37 Build a chart that reflects data from the web query.

After the chart is built, change the properties of the web query by right-clicking a cell in the imported data and choosing Data Range Properties.

In the External Data Range Properties dialog box that appears, select the Refresh Every check box. Change the spin button from 60 minutes to 1 minute. In addition, select the check box for Refresh Data When Opening the File (see Figure 6.38). When you click OK, the web query begins to refresh every minute and automatically update your chart in the dashboard.

It is possible to build a dashboard with many different web feeds and charts that update automatically as long as the Internet connection remains live. You can accomplish this without writing any VBA macros.

Making Charts Small for Use in Dashboards

The goal of creating an executive dashboard is to fit a lot of data into a single screen of data.

Figure 6.38 Excel updates the web query every minute.

Beginning with Excel 2007, Microsoft added features that makes it easier to scale charts down until they are quite small. In Figure 6.39, the chart at the top is converted to a smaller chart in the lower right by using Layout 11 from the Chart Layouts gallery on the Design tab. The two other charts are not really charts at all, but Sparklines.

→ See Chapter 9, “Using Sparklines, Data Visualizations, and Other Nonchart Methods,” for examples of how to create these tiny word-sized charts.

Figure 6.39 Beginning with Excel 2007, it is easier to create small charts for dashboards.

Next Steps

In this chapter, you learned that there are times when you need to think creatively to coax an “impossible” chart out of Excel. In Chapter 7, “Advanced Charting Techniques,” you will learn how to use Excel’s built-in charting tools to create charts that you do not normally see in Excel.