Assignment #1
Raw Data and Moving Averages
In this assignment you will study a particular pair of moving averages applied to historical data for the settlement prices of the futures contract being studied. You will pick a contract to study in class from a selection provided.
Collect the settlement prices from all the trading days in February 2017 through March 25, 2017from https://www.barchart.com/futures/major-commodities For example, the circled close price (a.k.a. settlement price) of CCK17 on Feb. 1, 2017 is 2112. You will need to move the cursor and point it to the stick on the chart to collect the last price for a particular business day for a time period from Feb 2017 through March 25, 2017.
CCK17means Cocoa, May, 2017. Use June (M) contract for your project.
Start a worksheet listing the dates and the settlement prices. Make sure you list the data starting with February 1 and going down to the March 25 data.
Calculate a 3-day moving average, 3-day MA, for the settlement prices in the column next to the settlement prices.
[Example: =sum(b9.b11)/3]
Note that the 3-day MA will have two missing points in the beginning. Format the averages to the same precision as the original data.
Then calculate another moving average in the column next to the 3-day MA. Use 6-day, 8-day, 10-day as you selected.The 6-day MA will have five missing points in the beginning, etc.
Note that you are not using the moving averages to make forecasts. You are using them to describe past and current data. Therefore the moving average formula in any cell should include the data for the same date next to it (not the previous date as would be done for forecasting). See the example below for what your data and moving averages should look like.
You will be adding more columns of calculated data to the right of the moving average data.
Create a line chart (without markers) on a separate worksheet which shows the settlement prices, 3-day moving average, and the specific other moving average.
When selecting the columns of data you have to include the empty cells with missing data (don't put zeros in the cells) when you specify the data series so that the plot of the moving averages is not shifted to the left. Also, copy cell contents to the end of March.
Use the Layout menu under Chart Tools to modify the chart. Don't just accept Excel's default settings and format; the defaults in Excel can be fairly crude, even awful, and make for poor presentations. Make your chart resemble the chart in the example.The same format makes comparing the charts to each other in class much easier. Use a two-line title, label the axes.Some futures contracts do not have units; they are simply an index.
Use a solid line for the settlement prices, a dashed line for the 3-day MA, and a dotted line the other MA.Right clicking on a line will allow you to format the data series. Use dashed gridlines and do not shade the plot area.
Put the legend on the bottom. Put a border around the plot area. Use major and minor tick marks. Have "Feb" and "Mar" show up on the horizontal axis scale.
In your charts emphasize the plot areas and not the titles and labels. Fill up the page with the chart. Paste as a picture, not an Excel object, into the Word document.
Create a report in Word. Include a cover sheet, the display of each dataset (without gridlines), a printout of the cell formulas(with border row and column headings and gridlines), and the chart. Use portrait for the display of data and cell formulas and landscape for the charts. The report must be presentable and effective, and deficiencies will be graded.
Drop off the report to dropbox along with the spreadsheetsthemselves.
The following images are examples using old data to demonstrate excel work you should do for your futures contract.
Attachment:- Financial Futures and Options.rar