Calculate a 3-day moving average

Assignment Help Finance Basics
Reference no: EM131467154

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

Verified Expert

Futures contracts is the standardized contract which can be traded between the two parties in place. Japanese yen currency enable traders to buy and sell the product at the agreeable price.

How does ford initially value its debt

MFE6110 - Long-term liabilities How does Ford initially value its debt on the balance sheet? How are the discounts, premiums, and costs directly related to the issuance of

Find the area under the normal curve

If all the recommendations are independent and the newsletter writer‘s skills is as claimed, what is the probability of observing four or fewer profitable recommendations

Calculate the expected utility for both investors

FINC3017 Investments and Portfolio Management - Calculate the optimal portfolio for both investors that consists of the following five industries: MATL, CONS, TELE, UTIL and

Conduct stock valuations employing statistic models

Conduct stock valuations employing statistic models covered in the class. For example, Single-index market model, and discounted dividend model (DDM) - characteristics of th

Calculate a 3-day moving average

BADM 418 - Financial Futures and Options - Spring 2017 - Calculate a 3-day moving average, 3-day MA, for the settlement prices in the column next to the settlement prices.

Complete a loan servicing calculation

Complete a loan servicing calculation (NSR) by completing the form overleaf. Remember this NSR calculation is based on 2 security properties - Complete the lender's applicat

Examine the effect of regulatory issues

FIN 961: Derivatives - Develop comprehensive theoretical, industrial and technical knowledge of financial derivatives offered in global markets such as options, futures, for

Calculate marginal tax rate and taxable income

Salary \$22,000.00 Corporate Bonds \$2,000.00 Muni Bonds \$10,000.00 Ordinary Dividends \$3,000.00 Qualified Dividends \$3,000.00 ST Capital Gain \$150.00 LT Capital Loss \$1,500.0

Reviews

len1467154

4/19/2017 2:28:59 AM

The first attachment is the assignment that is due this Friday. The second attachment is the instructions to the previous assignment. The third attachment is the solution to the previous assignment, which is needed for this second assignment. This assignment is due by Friday. 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.