Adding volume data to an Excel candlestick chart

The most difficult type of add-on for Excel candlestick charts is a volume chart. There are a lot of additional steps involved with this operation, and if it gets to be too taxing, you may want to leave the volume charts to the free online charting services described earlier in this chapter or the charting packages discussed at the end of the chapter.

Formatting the data for your chart

In order to create a chart with candlesticks on top and volume on the bottom (the standard presentation), download the data from Yahoo! Finance in its raw form and then continue with the following:

1. To begin formatting the data in the correct order (Date, Volume, Open, High, Low, Close), start by deleting column G (Adj. close).

2. Highlight column B, click Insert, and select Columns.

All columns except column A (Date) will move to the right, and column B will be empty.

3. Highlight column G, click Edit, and select Copy.

4. Highlight column B, click Edit, and select Paste. Column B should now contain your Volume data.

5. Highlight column G again, click Edit, and select Delete. Column G will be empty.

6. Highlight columns A through F, click Data, and select Sort.

Make sure that the Sort By choice is Date and the button next to Ascending is checked, and then click OK.

Creating the actual chart

Now you can make a chart with volume! The steps are very similar to creating the basic Excel candlestick chart:

1. Highlight all the data in your spreadsheet.

2. Click Insert, and from the available choices, select Chart.

Step 1 of 4 of the Chart Wizard appears.

3. Under Chart Type, scroll down and highlight Stock.

In the Chart sub-type, select the bottom right choice.

4. Click Next, which takes you to Step 2 of 4.

5. Click Next to open Step 3 of 4.

6. Select the Axes tab and make sure that under Primary Axis then Category (X) axis the button next to Category is selected.

7. Select the Gridlines tab and make sure that all boxes are deselected.

8. Click the Legend tab and make sure that the box next to Show legend is deselected.

9. Click Next at the bottom of the box to advance to Step 4 of 4. 10. Click the button next to As new sheet and click Finish.

The result should be a chart similar to Figure 4-9. Notice the overlap of the volume and the pricing data. You're almost finished, but to correct that overlap and make the chart more readable, you need to make some small adjustments.

Making the chart presentable

To make your chart fully readable, you need to adjust the range of the data on the left side of the chart by doing the following:

1. Right-click one of the gray row number cells on the left side of your chart and click Format Axis.

The Format Axis box will open.

Figure 4-9:

A basic Excel candlestick chart with volume before final adjustments.

Figure 4-9:

A basic Excel candlestick chart with volume before final adjustments.

Volume Chart Excel

2. Click the first two boxes under Auto to deselect Minimum and Maximum.

Make sure the Minimum is set at 0. For Maximum, take the default number and multiply it by 3. In my IBM example, the maximum is 16,000,000, so I would replace that with 48,000,000.

Your chart should now have the volume data clearly visible on the bottom and the pricing action set nicely at the top. Your final result should look like Figure 4-10.

0 0

Responses

  • tytti
    Can i add columns in candlestick chart?
    12 days ago

Post a comment