Dakota State University
BUS
418 Financial Futures & Options
Spring 2001
Computer Assignment #1
Charting Futures Prices
| Assignment Due: |
Tuesday, January 23, 2001 10:00 pm
|
| Assignment Software: |
Excel |
Procedure
Create a spreadsheet that will tabulate the following futures market data for a contract: Date, Open Interest (OI), Open, High, Low, and Settle. Use real data for the contract you are studying starting January 15, 2001 (through March 23, 2001 eventually). Use the June contract, not the March contract. Some contracts do not have a June expiration date. Use whatever is the first contract after March. Do not center the data in the cells. Format all numbers to the same number of decimal places.
Produce two charts. One chart should be a candlestick chart showing open interest and open-high-low-close data. The other chart should be an I-bar (I-beam) chart showing the same data. The charts should have all three axes labeled, a two-line title, and appropriate and easily read scales. The open interest bars must not overlap the prices. Gridlines must match tick marks on both y axes.
Make the candlestick chart first. Highlight five columns of data (volume through settle) down to the end of the time series (March 23). There should be no gaps in the data series. If there is a holiday eventually, delete that row in the spreadsheet. Select the Chart Wizard and follow the prompts. For type of chart choose "stock" and the type in the lower right-hand corner (volume-open-high-low-close). Data range is the selected five columns. Data series are in columns. Category (X) labels are the dates in the A column. Do not display legend. Add a two-line main title (make one line in the dialogue box and add a second one later) including name of contract, month of expiration, and type of chart. Label each axis appropriately. (Don't just copy the labels from the attached examples; they apply only to Eurodollars). Take off unnecessary 0's but format futures prices to the appropriate number of decimal places. Show units. Do not shade plot area. No italics. No headers or footers. Open interest bars must be well below prices. Open interest axis must start at zero. Do not make the I-bars bold. If the open interest bars overlap the prices, increase the size of the scale of the open interest; the bars will diminish. Add dashed major gridlines associated with the prices, not with the open interest. You have to adjust the ranges on the two Y axes so that the gridlines, which are attached to the left axis (open interest), exactly hit tick marks on both Y axes. Choose a range for the secondary Y axis such that the price movement of the futures contract will not be exaggerated. See examples.
To make the I-bar chart make a copy of the first chart (Hold
down Ctrl key and drag the chart page to a new location). Remove the
"open" series: click on one of the "open" data series points
and "delete." Then select chart type from the chart menu and choose
volume-high-low-close (lower left-hand corner). Add the "open" series
back in by selecting Add Data from Chart Menu and inserting cell references for
the C column data, all the way to the end of the data series again. Assign a
"-"marker to the new series so it is visible on the chart. To do this,
you have to right click on one of the "open" data points even though
it is not visible. Look up a value from the data sheet and search for it on the
chart at 200% zoom. Then "format" that series. There is no left hash
mark available; use the full hash mark, black foreground color, 3 pts.
The attached sheets show sample data and examples of candlestick and I-bar charts for the same data. As always, feel free to consult with me about this assignment. Different contracts can produce numbers quite different from these examples. The I-bar chart particularly can be challenging.
Emphasized Specifications
We are going to be comparing everybody's chart, so it is important that the format of the charts be consistent for all students.
The Open Interest scale should have a minimum of zero.
Use the June contract, not the March. Some contracts may have to use April or May dates.
There should be no gaps in the data series. If an automatic fill program is used to generate the dates, you will have to modify the dates.
The axis labels must contain the units.
Take off a lot of trailing zeros after the decimal point on the prices. Format all numbers to the same number of decimal places. Be consistent with the way the prices are reported in the Wall Street Journal.
Don't make the I-bars bold.
Use a full dash marker for the open price; use a half-dash marker for the settlement price.
Include dates on the X axis through March 23 when you make the chart. That way you won't have to keep updating the dates on the charts.
Use dashed gridlines. Gridlines must match up with prices and open interest tick marks.
Do not shade the plotting area.
The titles and scales should not overwhelm the charts. Use smaller fonts and expand the plotting area.
Make sure Jan, Feb, Mar show up on the Date axis.
Open Interest bars must not overlap prices.
TURN IN
1. A hardcopy of the spreadsheet.
2. A hardcopy of each of the two charts.