Assignment #1
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.
Assignment #2
Part A: Buy and Sell Signals
Make a copy of the spreadsheet in Assignment #1. That way you will always have the original if you make mistakes with this assignment. You will always be able to start over.
Add two new columns to the spreadsheet, one for the long or short position and another for commissions. The following uses the 3-day and 8-day moving averages as an example:
Use the initial data to determine whether you should start out long or short. For example, if on the eighth day the 3-day moving average is below the 8-day moving average, start out short on the first day. If on the eighth day the 3-day moving average is above the 8-day moving average, start out long on the first day.
Use the data and chart to determine whether you should be long or short the contract at any given time. When the 3-day moving average crosses the 8-day moving average going up, it is a signal to buy. When the 3-day moving average crosses the 8-day moving average going down, it is a signal to sell. In the examplebelow a buy signal was generated on Feb. 26. So on Feb. 27, the day after the signal was given, the position was switched to long. (Hence the position had to be short before that.)Remember that you are using settlement prices to detect the signals, so the signals are coming at the end of the day. So you can only make the switch the day after the signal.
The signals are easier to spot by looking at the data rather than at the chart. In the attached example three signals were generated during the study. You may find many more or fewer signals generated in your study.
Each time you switch from long to short or vice versa, charge your account $14 commission as a cash outflow. See the example spreadsheet. The formatting of the number can wait until you find out what values your marking to market generates.
Part B: Account Equity Data
Add columns for the change in price, the profit or loss for the day, and the cumulative profit or loss.
Use a formula to calculate the change in futures price each day. (The actual change, not the percent change.) Always trade just one contract. Format with as many decimal places as required from the way your futures contract price is quoted. Do not round off either in the calculation or the display.
Use a formula to calculate the profit or loss for the day, e.g., =IF(E12="L",G12*10,-G12*10) for cocoa. (See Excel Tips for an explanation of if/then formulas.) For cocoa each tick (a change of $1/ton in the futures price) represents $10 profit or loss. Whether that is a profit or a loss depends on whether the position is long or short. In the example there was an increase in price of $45/ton on February 2. Because the position was short one contract and the price went up, the equity went down, by $240. The if/then formula automatically takes the long or short into consideration when calculating profit or loss. Note that different contracts will produce numbers rather different from the example with cocoa. Check the Announcements page for the factor to use for your contract. This column of numbers should be formatted to the nearest penny if profit or loss is not always in whole dollars, as it is with cocoa.
Put zeros in the first row for change in price, profit or loss, and cumulative profit or loss. In this exercise you are considering only the change in equity in the account, not the equity in total.
Use a formula to increase or decrease the cumulative account equity as the case may be. This column of numbers should also be formatted to the nearest penny if profit or loss is not always in whole dollars. Remember to include the commission column in the calculation.
Note that in this example the buy/sell signal technique worked poorly and generated a loss. I also tried 3-day MA versus 10-day MA and it turned out to be profitable. In general the fewer buy and sell signals generated, the more profitable the technique.
Create a report in Word. Include a cover sheet, the display of data, and the cell formulas. . Use portrait for the display of data and landscape for the chart. Modify the spreadsheet and especially the chart from Assignment #1 to reflect suggestions or corrections needed in the grading of Assignment #1. Make sure to expand the column widths so the entire formulas can be read. Always include border row and column headings when printing cell formulas.
Attachment:- Data MAs.rar