OPRE 207 Project #1 TECHNICAL ANALYSIS Complete Solution

Question: #1201

OPRE 207 Project #1
TECHNICAL ANALYSIS

1 Introduction
In this assignment you will employ data smoothing techniques to a \real world" problem in
stock market analysis. The spreadsheet that you develop will be used as a building block for
Project 2. The ultimate intention is to investigate whether a proposed strategy for buying and
selling stocks really helps you earn more money.

2 Background
Generally speaking, investors can be divided into two groups: technical investors and fundamen-
tal investors. Technical investors believe that all relevant information about the future price of
a given security (usually a rm's stock) is embedded in the price history of that security. Fun-
damental investors disagree. They believe that most relevant information about the future price
of a given security (a company's stock again, in this instance) is embedded in the rm's nancial
statements and in a qualitative assessment of the rm's strategic position. In this assignment, you will prepare a spreadsheet model that calculates the returns from some strategies that technical investors use to make investment decisions. Then, in Project 2, you will determine if these strategies can generate statistically signi cant net pro ts using hypothesis testing.

3 Obtaining Data
There are numerous on-line sources for stock market data. One of the better ones is Yahoo!
Finance. Their archive of historical stock quotations is most easily accessed by going directly to
the URL for the Yahoo! Finance homepage, http://finance.yahoo.com/ The system is fairly simple. You input the stock ticker symbol of the company for which you want data. (If you don't know the ticker symbol, there is a lookup function available.) Then click on historical prices on the left side. You specify the time period of interest and indicate the frequency of data desired (daily, weekly, monthly, etc.). Click the Get Quotes button, and the data appear on your screen. At the bottom of the table is an option whereby you may \Download to spreadsheet". Clicking on this will save the data to a location you specify. Note that the default lename is TABLE.CSV. This is a comma delimitated le. You must use the \Save As" option to change the name and save as an Excel le. Use the Adjusted Close Prices as these have been adjusted for stock dividends and splits. The Yahoo! Finance homepage is a good source for general information about the company.Again, you input the ticker symbol. This will lead you to a page of current information on company stock price. More importantly, there are several links available. The \Pro le" link will take you to a page of detailed information on the company. Some statistical information (including the beta coecient of the stock) can be found under \Key Statistics".

4 Statistical Details

Moving averages are one of the oldest technical indicators in existence. A moving average is simply the average value of a certain phenomenon over a period of time. Consider a 10-day moving average. For example, if you want to calculate the 10-day moving average for your stock on April 1, 2011, you simply take the adjusted closing price of the stock on April 14 and sum
that price with the closing prices of your stock on the previous 9 trading days. You divide this sum by 10 and obtain the 10-day moving average for the stock. A rule of thumb among technical investors is as follows: If the 10-day moving average of a stock is above the 30-day moving average and both averages are moving upwards, then you should buy that stock. If the 10-day moving average of a stock is below the 30-day moving average and both averages are moving downwards, then you should sell that stock.

5 Your Mission

Please collect technical data on one stock selected from your rst project for the time period from Jan 1st, 2005 to Dec 31st, 2014. You need to construct a spreadsheet that will make buy and sell decisions for you for the stock. From that spreadsheet, you will then compute the return you would obtain from following your technical analysis strategy.

All calculations has to be done with formulas in excel so that for Project 2 you will easily be able to calculate returns for 10 stocks. (Grading will based on formulas you use in excel.) First, select one publicly traded stock from your rst project. The stock should be one that has been in existence long enough that you will be able to obtain daily market transaction data on that stock for the Jan 1st, 2005 to Dec 31st, 2014 time period. Second, obtain historical data on that stock. Use the adjusted close prices as these are adjusted for stock dividends and splits. You will want daily data for Jan 1st, 2005 to Dec

31st, 2014. You will want to save the data le locally, for further analysis. Read the data into Excel. Notice that your data are in order from most recent to oldest. The rest of the assignment will be easier if you sort the data in time order, from oldest to newest.
In your spreadsheet, calculate the 10-day and 30-day moving averages for as many days as possible. Note that you cannot begin calculating the 10-day moving average until you have 10 data points and you cannot begin calculating the 30-day moving average until you have 30 data points.
You now need to identify two events: Buy and Sell. Use Boolean (\IF") statements to have
Excel determine whether or not these events are occurring. De ne the events as follows: BUY: the 10-day moving average is greater than the 30-day moving average and both moving averages are increasing (higher than the day before)
SELL: the 10-day moving average is less than the 30-day moving average and both moving averages are decreasing (lower than the day before)
Once that's done, you need to compute the return you would achieve, if you followed the
technical analysis strategy. Look down the column with \Buy" and \Sell" recommenda-
tions until you nd the rst \Buy." Note the price.Now nd the rst \Sell" after that \Buy." Note the price again:
{ If, for example, you \Buy" at $20 and \Sell" at $25, for each dollar you have invested
you collect 25/20=1.25 or a return of r = (1:25 􀀀 1) = 0:25.
{ If you \Buy" at $25 and \Sell" at $20, for each dollar you have invested you collect
20/25=0.80, i.e a loss of 0.2 which could be referred to as a return of r = (0:8􀀀1) =
􀀀0:2.
This calculation is easily done by hand, but since you will want to do it for many stocks in project 2, develop formulas in Excel to do this automatically. If you have multiple \Buy" and \Sell" situations, you will need to do the preceding calcu-
lation on each transaction and then compound them. { Thus, if you have, for example, returns of r1 = 0:2, r2 = 0:3, and r3 = 􀀀0:1, we see from multiplying (1 + r1) (1 + r2) (1 + r3) = 1:2 1:3 0:9 = 1:404, that if you invest $1, you collect (1 + rtotal) = $1:404 in the end; your return in percent is rtotal = 1:404 􀀀 1 = 0:404 or 40.4%. In other words, we multiply the gross returns together.
To see whether the technical analysis strategy works, you want to see if you make more money using this strategy than you would without it. For comparison, calculate the return you would earn if you buy the stock on Jan 1st, 2005 (or on the rst date in your data set) and sell on Dec 31st, 2014 (or on the last date in your data set).

6 Help with Excel
The data you download is a .CSV le. You must use \Save As" to save as a Microsoft Excel le with a .XLSX le extension. If you submit a .CSV le, you will not receive credit for the assignment. Use Excel to do all of your computations. You can drag or copy formulas that need to be used repeatedly. To raise a number to a power, use the \^" operator or the POWER function. POWER(5,2)
or =5^2 calculates ve squared (25). POWER(5,1/2) or =5^(1/2) calculates the square root of 5.
You also need to use the Adjusted Close price for your calculations. The following functions
may be helpful.
AVERAGE(number1,number2,...)
Number1, number2, ... are numeric arguments for which you want the average. This
function is useful for computing the moving averages.
PRODUCT(number1,number2,...)
Multiplies all the numbers given as arguments and returns the product. This function is useful for compounding the return if you have multiple \buy" and \sell" situations. IF(logical test,value if true,value if false)
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas. Logi-cal test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
AND(logical1,logical2,...)]
Logical1, logical2, ... are conditions you want to test that can be either TRUE or FALSE.
AND(logical1,logical2, ...) returns TRUE if all its arguments are TRUE; returns FALSE
if one or more argument is FALSE.
The technical analysis strategy involves making decisions based on several criteria. Using the IF statements may seem complicated to do, but they are fairly simple once you get the idea. The easiest way is to use multiple IF statements. ( I did not include "$" in the formulas in following table. You need to think about when to freeze a cell if you plan to do copy and paste in Excel) For example, based on the 3 criteria, you could make a Buy column and enter in cell G35 the
formula
= IF(E33 F33 G33 = 1; \Buy"; \ ")
This is straightforward, but somewhat tedious. Alternatively, you can use nested IF statements
in the formula eliminating the need for columns E, F, and G
= IF(C33 > C32; IF(D33 > D32; IF(C33 > D33; \Buy"; \ "); \ "); \ ")
It is also possible to use the AND function to make this a bit simpler
= IF(AND(C33 > C32;D33 > D32;C33 > D33); \Buy"; \ ")

Once you have Buy and Sell columns (or a Decision column with both the Buy and Sell decisions), you can calculate your return. This is easy to accomplish if you use a \Bought at" column. This would be the price that you bought the stock. If you haven't yet bought or have sold and not bought again, this column should have a 0 or blank cell. You can only buy if you don't already own the stock and you can only sell when you own the stock. You will need to use IF statements to calculate the values for this column. You can use multiple IF statements, nested IF statements, as well as the AND function. Many di erent ways will work. A sample portion of a worksheet is illustrated at the end of the le.

7 Submitting your assignment
To submit your results click on Project 1 in Blackboard > Assignments > Project1. For each group, only one of the group members needs to submit the project. Please submit the Excel le that you used to calculate the returns. Include rst names of group members in the Excel le name (e.g. Adam William Project1.xlsx). Also, put full names of all members in the group on the top of the excel le.

8 EXAMPLE
In addition, you can nd a calculated example on Blackboard. This corresponds to Walt Disney stock so please do not select this stock for your assignment. The Excel le does not contain formulas just the numerical values, you may want to try to nd out which formulas I used and replicate the results, then use it for your own stock.

Solution: #1197

OPRE 207 Project #1 TECHNICAL ANALYSIS Complete Solution

1/31/2005   43.22
2/1/2005   42.48
2/2/2005   41.88
2/3/2005   35.75
2/4/2005   35.72
2/7/...