Creating an OHLC Chart in Excel 2007:-

The fundamental barrier to creating a true OHLC chart is that Excel does not offer a leftfacing dash as a built-in marker for a chart. You can, however, import your own image as a marker.

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. You follow these steps to create an OHLC chart in Excel 2007.

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 ribbon, choose 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 ribbon, choose 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, choose Solid Fill. Choose black from the color dropdown.

7. For the Marker Line Color category, choose Solid Line. Choose black from the color drop-down.

8. Without closing the Format dialog box, choose Series Open from the Current Selection drop-down on the Layout ribbon. Again select the Marker Options category.

9. Change the Marker Type setting from None to Built-in.

10. In the Type drop-down, choose the 10th 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. Choose Picture or Texture Fill. Excel updates the chart to show the default brown paper texture. Don’t worry, you will fix this.

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, choose No Line. Click Close to close the Format dialog box.

15. On the Layout ribbon, choose Legend, No Legend.

16. Choose Chart Title, Above Chart. Type the title AAPL 2006.

17. Resize the chart so it is horizontally smaller.

18. Right-click the numbers along the vertical axis, choose Format Axis, and then choose Minimum, Fixed, 50. (If you are going to later add volume to the chart, you could allow the vertical scale to run from 0 to 100. If you are only showing OHLC on the chart, you can scale the vertical axis from 50 to 100.)

The final chart is shown in the Figure (given below).



(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 often 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 chose Save As Template from the Type group on the Design ribbon.