Creating Stock Charts in Excel PDF

Preview

Summary

Download Creating Stock Charts in Excel PDF

Description

Creating Stock Charts in Excel 1 CRE REATI ATI ATIN NG A STO TOCK CK CHA HAR RT Creating a stock chart in Excel can be challenging for beginners, as there are a few settings that don’t work write with raw data that comes from sites such as Yahoo! Finance or Google Finance. In this section, you’ll start with data that was imported (or pasted) into Excel and change it around so the stock chart can be completed with few problems. Knowing about the different elements in an Excel chart will be a big help here. 1. Get your data into Excel. a. If you download historic stock prices from a finance site, the data will be in a Comma Separated Values (.CSV) file. This file might not open correctly in Excel. The best way to handle a CSV file is to start with a blank Excel file, click the Data tab, choose From Text, on the far left of the Get External Data group.

b. Browse to the .csv file you want to import. c. Check to make sure the first line of data displayed in the next dialog box contains column headings (it usually does). Put a check mark in the box for My data has headers and then click Next. The choice for data type should say Delimited.

1

d. Remove the check from the box for Tab delimited text and place a check mark beside Comma.

e. Click Finish. f. Click OK to place the data in the existing worksheet if you started with a blank worksheet, or select New worksheet. There are also properties and other settings which can be set on this dialog box. The properties include having the data refreshed each time the file is Excel workbook is opened or every few minutes. If you are working with historic data, you should probably go into Properties and not save the query definition, and not have the data refreshed, because the data will always be the same and will be stored permanently in the workbook.

i. Tip: You’ll only want to refresh data if the data changes constantly.

ii. Click on Properties and ensure your settings are as follows when working with historic data:

iii. Click OK. g. Now your data will be in Excel. The columns will most likely be in the order of Date, Open, High, Low, Close, and Volume.

h. One problem is that the data is usually sorted by Date in descending order, which means if you created a chart, the newer values would be on the left instead on the right. So

first, sort the data by Date. If the Date heading is already selected like in the image above, just click the Sort button for Lowest-To-Highest ( ) on the Data tab. Now your data is in Excel, and sorted properly. If click the Insert and then choose a 2D Line chart, you’ll get something like this:

What you can mostly see is the chart of Volume, and because volume data is usually so large, the rest of the data points all lay on top of each other at the bottom of the chart. If you alter the selected data to remove the volume, you’ll be able to see all the prices showing as a separate series. You can change the data by moving the coloured range outlines in the data table or right-click the chart and choose Select Data, and remove the Volume series. This is what the data looks like with volume removed. Notice the coloured outlines showing that the chart is still active, nothing else has been selected, and we just changed the range for the selected data.

The 4 lines of the chart are probably too close together and are confusing, so perhaps you might only be interested in High and Low prices. You can click on any of the lines in the chart to select the data series and press the Delete key or Backspace key on your keyboard, or you can right-click on the chart and go into the Select Data screen to remove the Open and Close prices. However this will depend on what you would be interested in studying. Below is a look at the completed chart:

You can see that it wasn’t as simple as just inserting the right chart to get a result that we wanted.

1.1 CREA EATIN TIN TING G A HIGH-LOW-CLOS LOSE E CHART The basic technique of just having a data value selected and then inserting a chart won’t work if you specifically want one of the 4 Stock Chart types within Excel. The 4 stock charts available are:    

High-Low-Close Open-High-Low-Close (OHLC) Volume-High-Low-Close Volume-Open-High-Low-Close

Each type of chart has specific distinguishing features which include special markers to indicate the prices in a compact manner without having multiple lines. To create a chart of each type, the data has to be in the same order as the name suggests. That means to get prepared for a chart that will include volume, we have to move the volume column and insert it before the Open column. To do this, select the volume column including the heading and Cut the cells (or copy but then you’d have to delete the column later) and place it either before the Open column or the High column depending on the chart you want. However, let’s start with a High-Low-Close chart. Since simply inserting a chart with one cell selected won’t work, you’ll have to select all the cells and column headings you want in the chart. 1. Select the data starting from the top of the High column (the heading) to the bottom of the Close column.

2. Click Insert, then on the Charts group open the Charts dialog box, click All Charts, then click Stock. You can see that the first chart is a High-Low-Close, and it is the only one that will give you a preview. a. Tip: If you can’t see a preview, you will be unable to create the chart based on the current selection of data.

3. Click Ok. You’ll see the chart created:

Notice that the legend is sort of useless so could be removed. Also, the chart title needs changing or deleting, and the horizontal axis should be set to the date values. To change the horizontal axis (also

referred to as labels) you’ll need to use the Select Data dialog box and edit the horizontal axis range to include the date column,

1.2 CREATIN EATIN TING G AN OPEN-HIG IGH H-LOW-CLOSE CHAR HART T Using a similar technique, an Open-High-Low-Close chart can be created which shows all 4 data series as unique markers on the chart, eliminating the confusion with multiple lines:

Chart Title 125 120 115 110 105 100 95 1

2

3

4

5

6

7

8

Open

9

10 11 12 13 14 15 16 17 18 19 20

High

Low

Close

The default looks pretty boring, but the colour of each data series can be changed to make it a little more impressive: Changing the look of OHLC series elements: 4. Make sure the chart is selected (click on it if necessary). Just make sure not to select any other specific item in the chart. 5. At the top, notice a new group of tabs for Chart Tools being displayed. Click the Format tab. 6. On the far left, click the down-pointing arrow and choose Down-Bars 1 from the list in the Current Selection group.

7. On the Format tab, change the Shape fill to a desired colour. 8. Then choose Up-Bars 1 from the selection list and pick another colour. You can get results like the following. Another good idea is to save these settings as your own custom chart template (right-click the chart and choose Save As Template)

1.3 CREA EATIN TIN TING G A VOL OLUM UM UME E-OPE PEN N-HIG IGH H-LOW CHA CHART RT A basic VOHL chart will look a little strange with the default settings:

But volume is typically shown as an area chart, so click the blue bars in the chart to make sure the series is selected, and change the chart type using the new Combo option in Excel 2013 to make the volume an Area chart:

Although the ability to have combo charts was available in older versions of Excel, Excel 2013 does make things a little bit easier. With a little work you can get a nice chart with some impressive design elements thrown in in case you are making a presentation instead of just analyzing the data. Or if you just want to keep yourself from getting bored....