FINANCE 601, Fall 2023
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FINANCE 601, Fall 2023
A Major Assignment (25% of the Course Grade)
Due Date (Time): Week 10, Friday, November 17, 2023 (6:00 p.m.)
September 18, 2023
. Although the due date for the Microsoft Excel-based assignment ó which is an important component of FINANCE 601 from both pedagogic and practical perspectives ó is about two months after its posting date, I still strongly encourage you to start reading the instructions as provided in this Öle and to start gaining hands-on experience with data collection from Yahoo! Finance as soon as you can.
. Data collection and the attainment of usable data from the collected data properly will likely be time-consuming if this is your Örst experience with the tasks involved. The performance of the various analyses that are new to you and the writing of a Önal report of high quality will also require considerable amounts of time to complete. Thus, my advice is that you do not wait until the due date is fast approaching before paying attention to the assignment.
1 Introductory Remarks
The assignment is NOT a group assignment; rather, it is intended to be based on your own individual e§orts. Here is my practice for assessing individual assignments: Shouldn students hand in reports and the supporting Excel Öles that were considered to be virtually identical, each of these n students would receive 1/n of the average grade among them, regardless of who among them did the bulk of the actual work. I will continue with the same practice for the assignment,
for ensuring fairness to all students in the FINANCE 601 class.
Various individual tasks are required for the assignment. They include the collection of raw data from public sources; the attainment of usable data; the completion of each component of the intended analysis; the deduction of implications from the individual components of the intended analysis; and, Önally, the completion of a formal report at the professional level. You are encouraged to perform additional tasks that can strengthen your understanding of the practical topic of equity investments and can help you explore further insights from your experience in doing the assignment.
1.1 The Focus of the Assignment
The focus of the assignment is on the correlation of returns. This statistical measure is highly important in investment Öelds. For two random variables, labeled as X and Y; with n pairs of observations being (x1 ; y1 ); (x2 ; y2 ); : : : ; (xn; yn); the sample correlation is
rXY = sXY
where sXY ; sX ; and sY are the sample covariance of X and Y; the sample standard deviation of
X; and the sample standard deviation of Y; respectively, deÖned as
sXY = X(xi - x)(yi - y);
sX = X(xi - x)2 ;
sY = X(yi - y)2 ;
x = X xi ;
and y = X yi:
The sample correlation rXY is Pearson correlation coe¢ cient when applied to a sample. Named after Carl Pearson (1857-1936), it measures the linear association of X and Y: It does not have any units and is in the range of -1 to 1:
1.2 A Microsoft Excel-Based Assignment
The assignment requires the use of empirical observations, which are conÖned to historical informa- tion that is freely available from the Yahoo! Finance website (https://ca.Önance.yahoo.com/). The speciÖc tasks in the assignment are covered in individual sections below. As all computing and graphing tasks in the assignment are Excel-based, you are strongly advised to acquire, in ad- vance, essential Excel skills, such as di§erent types of copy-and-paste, multi-level sorting of data, commonly-used Excel functions, and various matrix operations. Excel functions such as STDEV.S and CORREL (for sample standard deviations and sample correlations, respectively) are useful for the purpose of the assignment. To use Excel matrix functions such as TRANSPOSE, MMULT, and MINVERSE (for transposition, multiplication, and inversion, respectively), it is important to
press Shift+Ctrl+Enter keys simultaneously; otherwise, the intended task cannot be performed.
2 An Exchange Traded Fund, XIU.TO
The ticker symbol XIU.TO is for the iShares S&P/TSX 60 Index ETF (which stands for Exchange Traded Fund). Its equity holdings cover 60 large companies that are listed on the Toronto Stock Exchange (TSX). As of September 12, 2023, XIU.TO covers 10 sectors of the Canadian economy. Listed below are the 10 speciÖc sectors, in bold font and with the total number of stocks and any trust units involved shown in parentheses. Also listed for each sector are the names of two stocks or trust units, with the corresponding ticker symbols in parentheses. They are typically those with the highest proportional holdings in each sector.
For ease of exposition, whenever the term ìstocksî is used below in the context of the assign- ment, it implicitly includes trust units that are traded on the TSX as well. The stocks for use in the assignment all have quarterly dividends; the only exception is the trust unit in the real estate sector, which pays monthly dividends. Here is the list:
1. Communication (3): BCE INC (BCE.TO), ROGERS COMMUNICATIONS INC (RCI- B.TO).
2. Consumer Discretionary (5): RESTAURANT BRANDS INTERNATIONAL INC (QSR.TO), DOLLARAMA INC (DOL.TO).
3. Consumer Staples (5): LOBLAW COMPANIES LTD (L.TO), METRO INC (MRU.TO).
4. Energy (9): ENBRIDGE INC (ENB.TO), SUNCOR ENERGY INC (SU.TO).
5. Financials (12): ROYAL BANK OF CANADA (RY.TO), TORONTO DOMINION BANK (TD.TO).
6. Industrials (6): CANADIAN NATIONAL RAILWAY (CNR.TO),WASTE CONNECTIONS INC (WCN.TO).
7. Information Technology (4): CONSTELLATION SOFTWARE INC (CSU.TO), OPEN TEXT CORP (OTEX.TO).
8. Materials (9): NUTRIEN LTD (NTR.TO), FRANCO NEVADA CORP (FNV.TO).
9. Utilities (5): FORTIS INC (FTS.TO), EMERA INC (EMA.TO).
10. Real Estate (2): CANADIAN APARTMENT PROPERTIES REAL ESTATE INVEST- MENT TRUST (CAR-UN.TO), FIRSTSERVICE CORP (FSV.TO).
. An Excel Öle containing a complete list of the 60 constituent stocks is available from the
following Blackrock iShare website:
https://www.blackrock.com/ca/investors/en/products/239832/ishares-sptsx-60-index-etf
2.1 Prices and Dividends Collected from Yahoo! Finance and Return Compu- tations
Prices and dividends of all stocks in XIU.TO are available from the Yahoo! Finance website (https://ca.Önance.yahoo.com/). Such data allow us to compute monthly returns of each stock over a given sample period. As free access to Yahoo! Finance requires neither memberships nor registrations for its users, it is the only data source that is acceptable for the purpose of the assignment. Notice that we must always use the extension ì.TOîwhen searching for stocks traded on the TSX. For example, the ticker symbol of Bell Canada Enterprises (BCE INC), which belongs to the Communication sector, is BCE. If we use BCE without the extension ì.TOî attached to it, the corresponding price and dividend data are for the stock traded in U.S. dollars on the New York Stock Exchange (NYSE). If we use BCE.TO instead, the corresponding
price and dividend data are for the same stock traded in Canadian dollars on the TSX.
Let us continue with BCE.TO as an example. Here are some historical daily closing prices (P) and a dividend (D) from Yahoo! Finance, with prices collected for 5 consecutive trading days:
The four daily returns are
R June 11; 2021 = $61:$6(66)39($6)1:39 = 0:00439811;
R June 14; 2021 = $60:77 +$($)6(0)1:(:8)66(75) 一 $61:66 = 一0:00024327;
R June 15; 2021 = $61:$6(35)77($6)0:77 = 0:00954418;
and
R June 16; 2021 = $61: $6(16)35($6)1:35 = 一0:00309698:
From the same data source, we also have the following:
Here, each price is the closing price of the corresponding month-end trading day. The return for
the month of May 2021 is simply
R May 2021 = = 0:02960413:
However, the return for the month of June 2021 is approximated by
R June 2021 = = 0:03652625:
This is only an approximation, according to the time value of money, because the ex-dividend date is prior to the month-end. The closer the ex-dividend date to the month-end, the better is the approximation.
Now, let us turn our attention to the details of collecting historical price and dividend data from Yahoo! Finance. Searching for BCE.TO there will lead to a tab for ìHistorical Data,î from which we can specify ìTime Periodî (with speciÖc ìStart Dateî and End Dateî among other choices), ìShowî (with choices being ìHistorical Prices, Dividends Only, or Stock Splitsî), and ìFrequencyî (with choices being ìDaily, Weekly, or Monthlyî). Once the choices have been speciÖed, the corresponding data in an Excel Öle with the extension ì.csvî (for comma delimited) can be downloaded. The same Öle can be saved as a regular Excel Öle.
For the purpose of computing monthly returns based on historical price and dividend data, it is NOT advisable to download directly from Yahoo! Finance monthly price data. If the choice is ìMonthlyî instead, the displayed (and downloaded) dates will become beginning-of-month dates instead. If such dates are uncorrected, they will cause errors in return computations. In the above
example, the correct prices but at the wrong dates from Yahoo! Finance are as follows:
Thus, it is important that we stay with ìDailyî when specifying ìFrequencyî and delete all non- month-end price data from the Excel Öle downloaded afterwards.
2.2 Data from Yahoo! Finance and Some Suggestions for Improving Compu- tational E¢ ciency
It is not a tedious task to delete all non-month-end prices from a chronological list of daily prices. For example, we can use the Excel function MONTH to indicate the month of a date. As the downloaded price data are all chronological, from the oldest to the newest, the use of the Excel
function MONTH will lead to a column of integers from 1 to 12, representing the 12 months of
a year. When there is a change of the displayed integers, from one row to the next, the date corresponding to the integer before the change must be a month-end. By using the Excel function IF, we can add an indicator (such as an integer) for each change and then paste, as values, the two columns whose cells contain the Excel function MONTH and the indicator. Subsequently, we can use multi-level data sorting in Excel to separate the month-end data from non-month-end data (and then delete the latter), without a§ecting the chronological order of the month-end dates.
Stocks normally trade on the TSX from Monday to Friday, between 9:30 a.m. and 4:00 p.m., Toronto time. For monthly return computations, we use month-end closing prices (around 4:00 p.m.) and any dividends during the month. If a month-end happens to be on Saturday or Sunday, then we use the Friday, which is one or two days earlier. If such a Friday is not a trading day on the TSX, then we use the Thursday that is one day earlier as the month-end instead. There are also rare occasions of trading halt due to various reasons. If trading halt occurs during normal trading hours and trading cannot be resumed later on the same day, then the closing prices from
Yahoo! Finance normally correspond to the Önal trades of the day.
There are seven columns in each set of downloaded price data, consisting of ìDate, Open, High, Low, Close, Adjusted Close, and Volume.î Only the data of ìDate and Closeî (in columns 1 and 5, respectively) are relevant, as ìCloseî shows the closing price of the stock on the date shown. Do not use ìAdjusted Closeî (in column 6) instead. If there are stock splits during the sample period, it is important to check whether the stock prices before the splits have been adjusted by Yahoo! Finance. This precautionary measure is to avoid erroneous return computations for the months of stock splits. For example, if a stock has a 2-for-1 split reported on a certain date, but the downloaded prices and dividends before and after the event date do not show approximately 2-fold changes, then it is safe to conclude that the data from Yahoo! Finance have been properly adjusted. DOLLARAMA INC (DOL.TO) in the Consumer Discretionary sector is such an example; it had a 3-for-1 split on June 20, 2018.
Notice that the downloaded dividends from Yahoo! Finance may not be chronological. A safe way to combine the chronological prices (from ìCloseî) and dividends for monthly return computations is to place them in two di§erent columns. If an ex-dividend date is also a month- end, the row for dividend in the Excel worksheet involved ought to be before the row for price there after sorting, so that subsequent return computations can be performed without complications.
To illustrate, let us consider the following example:
Here, column B is for month-end prices and column C is for any dividends during the months involved. If the formula for cell D23 is =IF(B22="",C22,""), this will give us 1.03 there. We can paste the formula to the remaining cells in column D to line up the month-end prices and the corresponding dividends during the month, if any. For cell E23, the use of the formula =IF(B23="","",1) will give us an indicator, which is 1, as cell B23 contains a price. We can also
paste the formula for cell E23 to the remaining relevant cells in column E, leading to the following:
Once we have replaced the relevant cells in columns D and E by the corresponding values, via Excelís copy-and-paste features, the rows involved can be sorted according to columns E and A. The subsequent return computations will be straightforward. The above approach works equally well even if none of the ex-dividend dates are in month-ends.
3 The Sample Covariance Matrix of Returns
For an n-stock case, let
R11; R12 ; : : : ; R1T ;
R21; R22 ; : : : ; R2T ;
.
.
.
Rn1; Rn2; : : : ; RnT
be the set of historical monthly returns, as computed from the price and dividend data over a T-month period. Here, each Rit is the return of stock i in month t; for i = 1; 2; : : : ; n and t = 1; 2; : : : ; T: The sample average monthly return of stock i is
Ri = X Rit ; for i = 1; 2; : : : ; n:
Let us consider an n X T matrix
2 (R11 一 R1 ) (R12 一 R1 ) . . . (R1T 一 R1 ) 3
= (Rn1 一... Rn) (Rn2 一... Rn) (RnT 一... RT ) :
With the prime denoting matrix transposition, the matrix product
2 (R11 一 R1 ) (R12 一 R1 ) . . . (R1T 一 R1 ) 3
T 一 1 = T 一 1 (Rn1 一... Rn ) (Rn2 一... Rn ) (RnT 一... RT ) .
2 (R11 一 R1 ) (R21 一 R2 ) . . . (Rn1 一 Rn) 3
6 7
6 . . . . . 7
4 (R1T .一 R1 ) (R2T .一 R2 ) . . .(.) (RnT 一. RT ) 5
reduces to the following n X n symmetric matrix:
P 一R1 )(R1t 一R1 ) 一R1 )(R2t 一R2 ) . . . 一R1 )(Rnt 一Rn) 3
6 . . . . 7 :
6 .(.) .(.) . . .(.) 7
一Rn)(R1t 一R1 ) 一Rn)(R2t 一R2 ) . . . 一Rn)(Rnt 一Rn) 5
t(s)rix multi(the sam)p(e)licatio(as the)n(s)o(a)f(m)with(varia)ne(c)a(e)ch resul(matrix)tu()sdiv(ing)ided b(the E)y(x)1(n)iw(o)il(n)l directly(MMULT) l(f)e(o)a(r)dtt(h)o(e)
The method below is based on Aneja, Chandra, and Gunay [Journal of Finance, 44(5), (1989), pp. 1435-1438]. The variance of returns of a portfolio consisting of n securities can be decomposed
into n individual variance terms and n(n - 1) individual covariance terms as follows:
σp(2) = Σ Σj(n)=1 xixjσij = Σ xi(2)σi(2) +Σ Σj(n)=1xixjσij
j i
= Σ xi(2)σi(2) +Σ Σj(n)=1xixjp ijσiσj ;
j i
where pij is the correlation of returns between securities i and j: Let
xi = ; for i = 1; 2; : : : ; n:
Notice that 1=σ1 ; 1=σ2 ; : : : ; 1=σn are not portfolio weights. However, we can deÖne a random
variable as a linear combination of thex i(tu):(s) of the n individual securities such that
The variance of is still given by equation (1), where
Here, as there are no restrictio(σ)n(ij)so(=)n(C)th(ov)a(i;)ters x(for i;); x2(=):; :2:;;:x n(: :);(;)w(n:)
a condition.
The variance of this linear combination is
σp(2) = Σ2 σi(2) +Σ Σj(n)=1 pijσiσj
j i
= Σ 1 + Σ Σj(n)=1pij
j i
= n +Σ Σj(n)=1pij:
j i
As the average correlation of returns based on n securities is
r = Σ Σj(n)=1pij ;
j i
we can write
σp(2) = n + n(n - 1)r
or, equivalently,
r = σp(2) - n :
Once σp(2) is estimated, an estimate of r can also be obtained.
consi(F)d(o)e(r)rtt(h)he(is)tti(a)m(sk) s(l)er(et)i re(a)t(n)u(d)rsbfr(e)o(t)m(h)eT observat(estimates)io(of)ns(σ)or(a)nth(d)e(r)rsecuritie(espectiv)s(e)l:y. To estimate σp(2) ; let us
R11; R12 ; : : : ; R1T to estimate σ 1(2)
R21; R22 ; : : : ; R2T to estimate σ 2(2)
.p(2) known, the average correlation optn2y r一一 rns can be obtained from
The volatility index VIX was introduced to the Önance world by Robert E. Whaley, as documented in the investment literature in 1993 (Journal of Derivatives, Volume 1, 1993, pp. 71-84). On
September 23, 2003, CBOE changed the VIX calculations. As a forward projection of volatility
in real time, the VIX is intended to predict the volatility of the S&P 500 index during the next 30 calendar days. Although the VIX is not tradable in Önancial markets, its current and past values (since January 2, 1990) are available from various sources, such as Yahoo! Finance (for ^VIX) and Federal Reserve St. Louis (https://fred.stlouisfed.org/series/VIXCLS) among others.
The computation of the VIX is based on a portfolio of out-of-money options on S&P 500. Op- tions for use in the computation must satisfy some speciÖc conditions. As option prices are available in real time, so are the computed values of the VIX. Some basic information about the VIX can be found in a Wikipedia article (https://en.wikipedia.org/wiki/VIX). There is also a 2019 CBOE White Paper, which illustrates the computation of the VIX (https://www.sfu.ca/~poitras/419_VIX. pdf).
The VIX is always positive. A value up to 12 indicates low volatility over the next 30 days. A value higher than 12 but lower than 20 is considered normal. A value that is 20 or higher indicates high volatility over the next 30 days. On October 24, 2008, which was during the US Önancial crisis, the intraday VIX reached an all-time high of 89.53. More recently, on March 12, 2020, which was during the early days of the Covid-19 pandemic, the VIX reached 75.47 at the close of the trading day.
Over time, CBOE has also introduced various other volatility indices. For example, there are extensions of the VIX for predicting the volatility of S&P 500 over di§erent time intervals, such as 9 days, 3 months, 6 months, and one year. As of now, historical values of the 3-month volatility index are freely available in Yahoo! Finance (for ^VIX3M) and Federal Reserve St. Louis (https://fred.stlouisfed.org/series/VXVCLS).
Available CBOE volatility indices are typically for major stock indices, such as Dow Jones Industrial Average, NASDAQ Composite Index, S&P 500 Index, and Russell 2000 Index (for stocks of smaller companies), but not for the individual constituent stocks. However, daily closing values of volatility indices for some well-known stocks such as Amazon, Apple, Goldman Sachs, Google, and IBM are still freely available. The availability of the various CBOE volatility indices has provided investors with an additional investment tool.
The VIX, which predicts the volatility of S&P 500 in the month ahead, has emerged as a gauge of fear or stress in the stock market. Rightly or wrongly, it is perceived by many in the investment world as the fear index. As such, a high value of the VIX is also perceived by these investors as a signal of imminent market declines, thus a§ecting their trading activities. Such interpretations of the VIX are contrary to the well-established notion that expected return and volatility are in
two di§erent dimensions of an investment. Even if the VIX is interpreted properly ó only as a measure of expected volatility in the month ahead ó its critics still question whether, in practice, it represents an improvement over simpler forecasting methods, given that many severe events in the future are unpredictable.
2023-11-13