Financial Portfolio Selection using Multi-factor Capital Asset Pricing Model and Importing Options Data

Category : Uncategorized

Financial Portfolio Selection using Multi-factor Capital Asset Pricing Model and Importing Options Data

Download the PDF from SSRN.
Examples: fetchcomponents.

Abstract

Diversification and portfolio selection is an integral part of finance teaching. In this study, multi-factor Capital Asset Pricing Model (CAPM) is estimated for components of Dow Jones Composite Index using data from Yahoo! Finance. Along with CAPM’s Beta, other statistics are calculated that are common decision criteria for portfolio selection such as historic standard deviation (total risk), total return, average daily return, Sharpe and Treynor measures. Two new commands are introduced, components and portfolio, that automate the entire process. A third new command, fetchyahoooptions, is provided to download and parse equity options data from Yahoo! Finance web pages and, optionally, to calculate the implied volatilities for the downloaded options.
Keywords: Finance, financial data, multi-factor CAPM, Beta, diversification, portfolio selection, Sharpe, Treynor, options, implied volatility

Introduction

Diversification, portfolio selection, hedging and Capital Asset Pricing Model (Sharpe (1964) and Lintner (1965)) (CAPM) are integral parts of finance teaching at undergraduate and graduate finance curriculum. Most textbooks provide detailed explanations about how to diversify, how to evaluate different financial securities and how to estimate CAPM. Considering the importance of “learning by doing” or “hands-on” approach, financial calculations and CAPM estimations are performed in classrooms as well as through assignments. There are abundant resources about these financial calculations and CAPM estimations mostly using Microsoft Excel. However, there seems to be limited resources for students to do financial calculations and CAPM estimation using an econometric software such as STATA. Considering the speed and ease of repeatability of STATA, this is an important void in finance teaching. Teaching should be more focused on theory and interpretation of results than long and tiresome steps of calculations and estimations.

It is true that MS-Excel is one of the primary software in the industry and an asset for finance students. By default, they should be able to estimate CAPM and other financial calculations using MS-Excel. However, automated tasks such as estimating CAPM for multiple stocks would require them to either learn MS-Excel macro programming or some type of econometric software. For the recent years, STATA became popular choice between academics and students, perhaps because of its ease of use or abundant resources available for STATA users.

The analysis performed in this study is of interest to finance instructors, students and investors (This study and associated STATA codes are for educational use. There is no direct or implied financial advise. While every effort is made for accuracy and reliability, the data and the results may not be accurate or reliable.). This study entails lecture notes to teach different criteria for portfolio selection, diversification and options. It shows how some of the most common statistics are calculated. It provides fast and easy commands to repeat these tasks during a study session or a lecture using STATA. It enables finance instructors to assign projects using real life data and spend time on interpretations and on methods proportional to their importance. The procedures provided with this study are also useful for investors (for educational purposes). Using real financial data, investors can compare their investment choices to achieve portfolio objectives.

In this study, the initial step is to obtain a list of stocks that make up the Dow Jones Composite Index. For this task a new STATA command is used: fetchcomponents. Then, historic prices for these stocks are downloaded using a STATA command fetchyahooquotes (Dicle (2011)). Using the same command, Fama-French factors are also downloaded to estimate multi-factor CAPM (Fama (1992) and Fama (1993)). The third step is to calculate average daily returns, total returns, standard deviation of daily returns, Sharpe (1970) (Sharpe) measure and Treynor (1965) (Treynor) measures. Within this step, multi-factor CAPM following Fama (1992) and Fama (1993) is also estimated for each stock. Another new STATA command is used to automate this process: fetchportfolio. The fourth step is the interpretation of the results. For the final step, as an introduction to hedging portfolio risk, options data are downloaded using a new STATA command, fetchyahoooptions, for a few stocks. Their implied volatilities are calculated and graphed.


Index components: fetchcomponents

There are several financial indices, such as S&P-500 and Dow Jones Industrial, available to investors. The list of stocks that make up these financial indices are usually referred to as index components. While some of these components may not be available, most of them are accessible via Yahoo! Finance web site. fetchcomponents downloads the list of symbols for the components of indices where available.

Syntax
*
fetchcomponents, symbol(string)
*


Description

fetchcomponents downloads the list of components for an index. List of stocks (components) are provided by Yahoo! Finance.

Options
  • symbol is the index for the components (i.e. ^NYA). There can only be one symbol defined which must be an index.


How to install
*
net from "http://researchbythenumbers.com/stata/010/"
*

Then, click on the fetchcomponents link and then “click here to install”.

Example #1: Usage
*
fetchcomponents, symbol(^DJA)
*

Screen Shot 2016-08-15 at 4.36.27 PM

fetchcomponents.ado
*
program define fetchcomponents, rclass
	
	version 10.0
	
	syntax , symbol(string)
	
	qui: {
		clear
		mata: get_components("`symbol'")
		drop if myvar=="@"
		gen Symbol=trim(myvar)
		drop myvar
		
		noi: di "Components for `symbol' is downloaded."
	}
end



mata:
	void get_components (string scalar symbol)
	{
		icerik = file_get_contents("http://researchforprofit.com/posts/stata_components.php?symbol=" + symbol)

		satir = tokens(icerik, "@")

		sutun=satir'
		st_addvar("str244", "myvar")
		st_addobs(rows(sutun))
		st_sstore(.,"myvar",sutun)

	}

	string file_get_contents (string scalar raw)
	{
		fh = fopen(raw, "r")
		raw=""
		while ((line=fget(fh))!=J(0,0,"")) {
			raw=raw+line
		}
		fclose(fh)
		return (raw)
	}

end
*


fetchcomponents.hlp
*
{smcl}
{* 15aug2016}{...}
{cmd:help fetchcomponents}
{hline}


{title:Title}
{p2colset 5 30 34 2}{...}
{p2col:{hi: fetchcomponents} {hline 2}} downloads the list of components for an index from Yahoo! Finance. {p_end}
{p2colreset}{...}


{title:Syntax}
{p 5 15 2}
{cmdab:fetchcomponents}{cmd:, symbol(string)}


{synoptset 20 tabbed}{...}
{synopthdr}
{synoptline}
{synopt:{opt symbol}} symbol for the index.{p_end}
{synoptline}
{p2colreset}{...}


{title:Description}
{p 5 5 2}
{cmd:fetchcomponents} downloads the symbols that are components of an index from Yahoo! Finance web site  {break}
(ex. http://finance.yahoo.com/q/cp?s=%5EDJA+Components).  {break}
Yahoo! Finance does not provide components for every index.  {break}
For instance, components of S&P500 cannot be downloaded with this command ({cmd:fetchcomponents_cnn}).  {break}


{title:Options}
{p 5 5 2}
{opt symbol} is the symbol for the index for which components will be retrieved.  {break}
These symbols are according to Yahoo! Finance web site. Ex: ^DJI and ^DJA


{title:Example}
{p 5 5 2}
{cmd: . fetchcomponents, symbol(^DJA)}


{title:Web support}
{p 5 5 2}
{browse "http://papers.ssrn.com/sol3/papers.cfm?abstract_id=2317589":{it:Download via SSRN}.}{break} 
{browse "http://researchbtn.com/?p=114":{it:More examples can be found at www.researchbtn.com}.}

{title:Author}
{p 5 5 2}
{hi:Mehmet F. Dicle}, Loyola University New Orleans, USA ({hi:mfdicle@gmail.com}){break} 
{browse "http://researchbtn.com":{it:www.researchbtn.com}}
*


fetchcomponents.pkg
*
d fetchcomponents -- Downloads the list of components for an index by parsing Yahoo! Finance web pages.
d 
d Program by 
d Mehmet F. Dicle, Loyola University New Orleans
d mfdicle@gmail.com
d
d fetchcomponents downloads the list of components for an index by parsing Yahoo! Finance web pages.
d 
d
d Created: 19sep2011
d Updated: 15aug2016

f fetchcomponents.ado
f fetchcomponents.hlp
*


stata.toc
*
d
d
p fetchcomponents Downloads the list of components for an index by parsing Yahoo! Finance web pages.
*



Options Data: fetchyahoooptions

Yahoo! Finance provides free financial data for the public use. While historic prices for most financial assets as well as some important statistics can be downloaded using Yahoo! Finance’s API (Stata commands fetchyahooquotes and fetchyahookeystats utilizes Yahoo! Finance’s API to download historic prices and key statistics.), some data are only available through web pages. This important data can be accessed via web browsers. However, to access the data as Stata usable data, these web pages need to be parsed (Parsing HTML pages is a common practice and some programming languages provide extensive language support such as PHP and Java script. There is a regular expressions parsing language that is adopted by most web programming languages.). Stata has a powerful and fast programming language: Mata. Even though it is intended as matrix programming language, it has HTTP protocol support, regular expressions and extensive string functions. It allows the newly introduced command fetchyahoooptions to fetch Yahoo! Finance options page, to parse the page and to process its contents to make them usable as Stata data. fetchyahoooptions also calculates implied volatility for downloaded options using Black (1973) option pricing formula following equations:

d_1=\frac{ln(S_{0}/K)+[(r_{f}+\frac{\sigma^2}{2})T]}{\sigma \sqrt{T}}

d_2=d_1-\sigma \sqrt{T}

c=S_{0} N(d_{1}) - K e^{-r_{f}T} N(d_{2})

p=K e^{-r_{f}T} N(-d_{2}) - S_{0} N(-d_{1})

Unlike equity prices, options data are not easily accessible for everyone as usable data. It is an important free service that Yahoo! Finance offers for public use. Options data are important for finance lecturers to be included in their lectures and teaching notes. They are important for researchers of financial derivatives. Although options data are only current and historical time-series are not available, they can be accessed daily and stored to create a time-series. Options data are also important for investors (for educational purposes). The implied volatility allows investors to have a sense of expected volatility in the market. Volatility smiles may allow investors to predict market direction.

Syntax
*
fetchyahoooptions namelist, [m(string) iv(real)]
*


Options
  • namelist is a list of ticker symbols for which the options to be parsed and downloaded from Yahoo! Finance’s options web page. Symbols are separated by spaces.
  • m(string) is the maturity date in which the options expire (i.e. 2016-09-16). Multiple maturities can be included (i.e. 2016-09-16 2016-09-23).
  • iv(real) is the calculated implied volatility using Black (1973) option pricing formula following the equations below. It uses a trial and error method to loop through levels of volatilities to calculate a call/put option price that matches the ask price. Implied volatility is calculated separately for each strike price.
    d_1=\frac{ln(S_{0}/K)+[(r_{f}+\frac{\sigma^2}{2})T]}{\sigma \sqrt{T}}

    d_2=d_1-\sigma \sqrt{T}

    c=S_{0} N(d_{1}) - K e^{-r_{f}T} N(d_{2})

    p=K e^{-r_{f}T} N(-d_{2}) - S_{0} N(-d_{1})

    In these equations, S_0  refers to the spot price of the underlying security, K  to the strike price, r_f  to the risk free rate (For the risk free rate, fetchyahoooptions downloads the current ^IRX, the 13 week U.S. Treasury Bill yield index, from Yahoo! Finance.), \sigma  to the standard deviation and T  to the years to maturity.


How to install
*
net from "http://researchbythenumbers.com/stata/010/"
*

Then, click on the fetchyahoooptions link and then “click here to install”.

HTML source code to Stata data transformation

Yahoo! Finance provides current prices of options for individual stocks through HTML pages. fetchyahoooptions utilizes Mata to access Yahoo! Finance options pages, parse them into string variables and then to turn them into usable Stata data. The following are some of the processes that fetchyahoooptions utilizes.

The following Mata function is used to get the HTML source code from the web as a string.

*
string file_get_contents (string scalar raw)
{
	fh = fopen(raw, "r")
	raw=""
	while ((line=fget(fh))!=J(0,0,"")) {
		raw=raw+line
	}
	fclose(fh)
	return (raw)
}
*


The following Mata function parses the current price of the underlying asset for the option.

*
string get_price (string scalar raw, string scalar ticker)
{
	bas_pos = strpos(strlower(raw), "") + strlen(ticker) + 20
	if (bas_pos<100) bas_pos = strpos(strlower(raw), "") + strlen(ticker) + 20
	if (bas_pos>100) { 
		output = substr (raw, bas_pos, .)
		son_pos = strpos(strlower(output), "")
		output = substr (output, 1, son_pos-1)
	}
	if (bas_pos<100) output = cat("http://download.finance.yahoo.com/d/quotes.csv?s=" + ticker + "&f=l1")
	output = subinstr(output, ",", "")
	return (output)
}
*


Above functions are called in the following order.

*
void get_options (string scalar symbol, string scalar month)
{
	unix_date = (date(month, "20YMD") - 3653) * (24 * 60 * 60)
	icerik = file_get_contents("http://researchforprofit.com/posts/stata_options.php?symbol=" + symbol + "&maturity=" + strofreal(unix_date, "%20.0f"))
	
	price = get_price(icerik,symbol)
	satir = tokens(icerik, "@")
		
	sutun=satir'
	st_addvar("str244", "myvar")
	st_addobs(rows(sutun))
	st_sstore(.,"myvar",sutun)

	stata("gen Price = " + price)
	icerik_rf = file_get_contents("http://finance.yahoo.com/quote/%5eirx?ltr=1")
	price_rf = get_price(icerik_rf,"^IRX")
	stata("gen IRX = " + price_rf)
}
*


The remaining string is parsed for individual HTML tags such as "td", "tr", "br" and etc. This parsing process is lengthy and can be accessed through the fetchyahoooptions.ado file. They are not provided here to conserve space.

The entire HTML source code downloaded from Yahoo! Finance is parsed into a single string using Mata. The string table that contains the options data has HTML tag "td" which can be used as line breaks which can then be converted into Stata observations. The following Stata code is used for this string split.

*
split myvar, parse("") gen(mfd)
*


The resulting dataset contains nine variables: Strike, Symbol, Last, Change, Bid, Ask, Volume, Open_Interest and IV_Yahoo.

Example #1: Usage

With this example, options data are downloaded for IBM and GOOG using fetchyahoooptions for the closest maturity (September 16, 2016) and the next closest maturity (September 23, 2016). The program will also calculate the implied volatility.

*
fetchyahoooptions GOOG IBM, m(2016-09-16 2016-09-23) iv
twoway (line IV Strike) if (Type=="Call") & (Underlying=="GOOG") & (Maturity==date("16sep2016","DMY"))
*



GOOG_options

Acknowledgments

I thank Jiad Alqotob, College of Business, Loyola University, New Orleans for his valuable comments during the creating of the fetchyahoooptions command.
I also thank Ashton Verdery, Department of Sociology, University of North Carolina at Chapel Hill for suggesting an improvement (implemented as suggested) in the fetchyahoooptions command to make it more reliable.

fetchyahoooptions.ado
*
program define fetchyahoooptions, rclass
	
	version 10.0
	
	syntax anything(name=tickers), [m(string) iv option_price(string) price_target(string) new_ask(string) target_day(integer 1) target_iv(integer 1) target_varname(string) price_change(integer 1) calconly progress]
	* m: expiration of the options, i.e. 2016-02-19
	* iv: whether to calculate the iv or not
	* option_price: price to use for calculations, i.e. Ask, Bid, Last
	* price_target (day): what the price needs to be to break-even after number of days passing using the calculated IV
	* price_target (iv): what the price needs to be to break-even after IV changes using the actual days to matuiry
	* target_day: criteria to use for price target, number of days (for day)
	* target_iv: criteria to use for price target, IV percentage change (for iv)
	* progress: show progress of the IV process
	
	if ("`calconly'"=="") local downloaded=0
	if ("`calconly'"!="") local downloaded=1
		
	if ("`option_price'"=="") local option_price="Ask"
	
	qui: {
		if ("`calconly'"=="") {
			* Download the options data for each symbol
			foreach name in `tickers' {
				* Download the options data for each maturity
				foreach tarih in `m' {
					clear 
					* Some symbols contain special characters (ex. .,-,^). These need to be kept the same for downloading the data from Yahoo! Finance
					* However, special characters need to be replaced to be used as Stata variables. 
					local name2 :subinstr local name "." "_", all
					local name2 :subinstr local name2 "^" "_", all
					local name2 :subinstr local name2 "-" "_", all

					mata: get_options("`name'","`tarih'")
					capture: split myvar, parse("#") gen(mfd)
				
					* Process downloaded data
					if (_rc==0) {				
						local downloaded=1
						replace IRX=IRX/100
						drop mfd1
						drop mfd2
						rename mfd3 Strike
						rename mfd4 Symbol
						rename mfd5 Last
						rename mfd6 Bid
						rename mfd7 Ask
						rename mfd8 Change
						rename mfd9 Change_per
						rename mfd10 Volume
						rename mfd11 Open_Interest
						rename mfd12 IV_Yahoo
						drop myvar
						drop if Symbol==""
						destring Strike Last Change Change_per Bid Ask Volume Open_Interest IV_Yahoo, replace force
						gen Underlying = upper("`name'")
						
						gen Type=""
						replace Type="Call" if ((substr(Symbol,length("`name'")+7,1)=="C") & ((strlen(Symbol)-15)==strlen(Underlying))) | ((substr(Symbol,length("`name'")+8,1)=="c") & ((strlen(Symbol)-15)!=strlen(Underlying)))
						replace Type="Put"  if ((substr(Symbol,length("`name'")+7,1)=="P") & ((strlen(Symbol)-15)==strlen(Underlying))) | ((substr(Symbol,length("`name'")+8,1)=="p") & ((strlen(Symbol)-15)!=strlen(Underlying)))
						gen Maturity=.
						replace Maturity=date(substr(Symbol,length("`name'")+1,6),"YMD",2100) if ((strlen(Symbol)-15)==strlen(Underlying)) 
						replace Maturity=date(substr(Symbol,length("`name'")+2,6),"YMD",2100) if ((strlen(Symbol)-15)!=strlen(Underlying))
						format Maturity %td
						order Underlying Maturity Type Symbol 
						save temp_0000_`name2'_`tarih'.dta, replace
						noi: di "Options data for `name' (`tarih') are downloaded."
					}
				}
			}

			* Append downloaded files
			if (`downloaded'==1) { 
				local ilk=1
				foreach name in `tickers' {
					foreach tarih in `m' {
						* Some symbols contain special characters (ex. .,-,^). These need to be kept the same for downloading the data from Yahoo! Finance
						* However, special characters need to be replaced to be used as Stata variables. 
						local name2 :subinstr local name "." "_", all
						local name2 :subinstr local name2 "^" "_", all
						local name2 :subinstr local name2 "-" "_", all

						if (`ilk'==0)  {
							capture: append using "temp_0000_`name2'_`tarih'.dta"
							if (_rc==0) erase "temp_0000_`name2'_`tarih'.dta"
						}
						if (`ilk'==1)  {
							capture: use "temp_0000_`name2'_`tarih'.dta", clear
							if (_rc==0) {
								erase "temp_0000_`name2'_`tarih'.dta"
								local ilk=0
							}
						}
					}
				}
			}
			capture: sort Underlying Maturity Type Strike		
		}
		
		if (`downloaded'==1) {			
			if ("`iv'"!="") {
				gen double T=(Maturity-date(c(current_date),"DMY"))/365
				label variable T "Years to maturity"
				sort Underlying Maturity T Type Strike		
				gen double d1=.
				gen double d2=.
				gen double Call=.
				gen double Put=.
				gen double IV=.
				label variable IV "Implied Volatility"
			
				forval bb=1/50000 {
					local aa: di %5.4f (`bb'/10000)
					* noi: di "`aa'"
					if ("`progress'"!="") {
						if mod(`bb',1000)==0 {
							noi: di `bb'/1000
						}
					}
					replace d1=(ln(Price/Strike) + ((IRX + ((`aa'^2)/2))*T)) / (`aa'*(T^.5)) if IV==.
					replace d2=d1-(`aa'*(T^.5)) if IV==.
					replace Call=round((Price*normal(d1))-(Strike*exp(-IRX*T)*normal(d2)),0.01) if IV==.
					replace Put=round((Strike*exp(-IRX*T)*normal(-d2))-(Price*normal(-d1)),0.01) if IV==.
					replace IV=`aa' if ((Type=="Call") & (Call==round(`option_price',0.01)) & (IV==.)) | ((Type=="Put") & (Put==round(`option_price',0.01)) & (IV==.))
				}
				drop Call Put d1 d2 T
			}
			
			if ("`price_target'"!="") {
				gen double T=(Maturity-date(c(current_date),"DMY"))/365
				if (strpos("`price_target'","day")) replace T=((Maturity-date(c(current_date),"DMY"))+`target_day')/365
				
				gen double IV2=IV				
				if (strpos("`price_target'","iv")) replace IV2=IV*(1+(`target_iv'/100))
				
				label variable T "Years to maturity"
				sort Underlying Maturity T Type Strike
				gen loop_stop=0
				gen Price_target=.			

				forval bb=-20000/20000 {
					local aa: di %5.2f (`bb'/100)
					capture: drop d1 d2 Call Put
					replace Price_target=Price+`aa' if (loop_stop==0) & (Type=="Call")
					replace Price_target=Price-`aa' if (loop_stop==0) & (Type=="Put")
					gen d1=(ln(Price_target/Strike) + ((IRX + ((IV2^2)/2))*T)) / (IV2*(T^.5))
					gen d2=d1-(IV2*(T^.5))
					gen Call=round((Price_target*normal(d1))-(Strike*exp(-IRX*T)*normal(d2)),0.01)
					gen Put=round((Strike*exp(-IRX*T)*normal(-d2))-(Price_target*normal(-d1)),0.01)
					replace loop_stop=1 if (loop_stop==0) & (((Type=="Call") & (Call>round(`option_price',0.01)) & (Call!=.) & (Ask!=.)) | ((Type=="Put") & (Put>round(`option_price',0.01)) & (Put!=.) & (Ask!=.)))
				}	
				replace Price_target=. if loop_stop==0
				replace Price_target=. if IV==.
				drop Call Put d1 d2 T loop_stop IV2
				
				if ("`target_varname'"!="") rename Price_target `target_varname'
			}
			if ("`new_ask'"!="") {
				gen double T=(Maturity-date(c(current_date),"DMY"))/365
				if (strpos("`new_ask'","day")) replace T=((Maturity-date(c(current_date),"DMY"))+`target_day')/365
				
				gen double IV2=IV				
				if (strpos("`new_ask'","iv")) replace IV2=IV*(1+(`target_iv'/100))

				gen double Price2=Price
				if ("`price_change'"!="") replace Price2=Price*(1+(`price_change'/100))
				
				label variable T "Years to maturity"
				sort Underlying Maturity T Type Strike

				gen d1=(ln(Price2/Strike) + ((IRX + ((IV2^2)/2))*T)) / (IV2*(T^.5))
				gen d2=d1-(IV2*(T^.5))
				gen Call=round((Price2*normal(d1))-(Strike*exp(-IRX*T)*normal(d2)),0.01)
				gen Put=round((Strike*exp(-IRX*T)*normal(-d2))-(Price2*normal(-d1)),0.01)

				gen new_ask=.
				if (Type=="Call") replace new_ask=Call
				if (Type=="Put") replace new_ask=Put

				drop Call Put d1 d2 T IV2 Price2
				
				if ("`target_varname'"!="") rename new_ask `target_varname'
			}
		}
		* capture: drop IRX
	}
	
	if (`downloaded'==0) {
		di as err "There are no option prices available for the selected symbols and maturity dates!"
	}
	
end



mata:
	void get_options (string scalar symbol, string scalar month)
	{
		unix_date = (date(month, "20YMD") - 3653) * (24 * 60 * 60)
		icerik = file_get_contents("http://researchforprofit.com/posts/stata_options.php?symbol=" + symbol + "&maturity=" + strofreal(unix_date, "%20.0f"))

		price = get_price(icerik,symbol)

		satir = tokens(icerik, "@")
			
		sutun=satir'
		st_addvar("str244", "myvar")
		st_addobs(rows(sutun))
		st_sstore(.,"myvar",sutun)

		stata("gen Price = " + price)
		icerik_rf = file_get_contents("http://finance.yahoo.com/quote/%5eirx?ltr=1")
		price_rf = get_price(icerik_rf,"^IRX")
		stata("gen IRX = " + price_rf)
	}

	string file_get_contents (string scalar raw)
	{
		fh = fopen(raw, "r")
		raw=""
		while ((line=fget(fh))!=J(0,0,"")) {
			raw=raw+line
		}
		fclose(fh)
		return (raw)
	}

	string get_price (string scalar raw, string scalar ticker)
	{
		bas_pos = strpos(strlower(raw), "") + strlen(ticker) + 20
		if (bas_pos<100) bas_pos = strpos(strlower(raw), "") + strlen(ticker) + 20
		if (bas_pos>100) { 
			output = substr (raw, bas_pos, .)
			son_pos = strpos(strlower(output), "")
			output = substr (output, 1, son_pos-1)
		}
		if (bas_pos<100) output = cat("http://download.finance.yahoo.com/d/quotes.csv?s=" + ticker + "&f=l1")
		output = subinstr(output, ",", "")
		return (output)
	}

end
*


fetchyahoooptions.hlp
*
{smcl}
{* 16aug2016}{...}
{cmd:help fetchyahoooptions}
{hline}


{title:Title}
{p2colset 5 25 30 2}{...}
{p2col:{hi: fetchyahoooptions} {hline 2}} downloads most recent option prices for a list of symbols from Yahoo! Finance. {p_end}
{p2colreset}{...}


{title:Syntax}
{p 5 15 2}
{cmdab:fetchyahoooptions}{it: namelist}{cmd:, [m(string) iv option_price(string) price_target(string) new_ask(string) target_day(integer 1) target_iv(integer 1) target_varname(string) price_change(integer 1) calconly progress]}


{synoptset 20 tabbed}{...}
{synopthdr}
{synoptline}
{synopt:{opt m}} maturity for the options page (2016-09-16){p_end}
{synopt:{opt iv}} whether to calculate the implied volatility{p_end}
{synopt:{opt option_price}} which option price (Last, Bid or Ask) to use in implied volatility calculations{p_end}
{synopt:{opt price_target}} whether to use days (day) or implied volatility (iv) for break-even price calculations{p_end}
{synopt:{opt new_ask}} calculate a new ask price for the options based on days passing (day) and/or implied volatility (iv) changing{p_end}
{synopt:{opt target_day}} criteria to use for {opt price_target}, number of days (for day) {p_end}
{synopt:{opt target_iv}} criteria to use for {opt price_target}, implied volatility percentage change (for iv) {p_end}
{synopt:{opt target_varname}} variable to generate for {opt price_target}{p_end}
{synopt:{opt price_change}} calculate a new ask price for the options based on percentage change in price{p_end}
{synopt:{opt calconly}} target and break-even calculations on already downloaded options data{p_end}
{synopt:{opt progress}} show implied volatility calculation progress{p_end}
{synoptline}
{p2colreset}{...}


{title:Description}
{p 5 5 2}
{cmd:fetchyahoooptions} downloads most recent option prices for a list of symbols from Yahoo! Finance{break}
Option prices are updated with 15-minute delay.{break}
Open interest is updated with one day delay.{break}


{title:Options}
{p 5 5 2}
{opt m} is the maturity for the options page (2014-09-16). Each monthly expiration page may contain multiple maturity dates for monthly and weekly expirations.

{p 5 5 2}
{opt iv} is to calculate the implied volatility. This is a time consuming and processor intensive process.

{p 5 5 2}
{opt option_price} is the option price (Last, Bid or Ask) to use in implied volatility calculations. 

{p 5 5 2}
{opt price_target} is to use days (day) or implied volatility (iv) for break-even price calculations.{break}
{opt price_target(day)}: What the price needs to be to break-even after number of days passing using the calculated IV{break}
{opt price_target(iv)}: What the price needs to be to break-even after IV changes using the actual days to maturity{break}

{p 5 5 2}
{opt new_ask} is to calculate a new ask price for the options based on days passing (day) and/or implied volatility (iv) changing

{p 5 5 2}
{opt target_day} is the criteria to use for {opt price_target}, number of days (for day) 

{p 5 5 2}
{opt target_iv} is the criteria to use for {opt price_target}, implied volatility percentage change (for iv) 

{p 5 5 2}
{opt target_varname} is the variable to generate for {opt price_target}

{p 5 5 2}
{opt price_change} is to calculate a new ask price for the options based on percentage change in price

{p 5 5 2}
{opt calconly} is to perform implied volatility, target and break-even calculations on already downloaded options data

{p 5 5 2}
{opt progress} is to show implied volatility calculation progress


{title:Examples}
{p 5 5 2}
Download options for AAPL for February maturities.{break}
{cmd: . fetchyahoooptions AAPL, m(2019-09-16)}

{p 5 5 2}
Calculate implied volatilities for already downloaded options data.{break}
{cmd: . fetchyahoooptions AAPL, calconly iv}

{p 5 5 2}
Calculate implied volatilities for already downloaded options data and show calculation progress.{break}
{cmd: . fetchyahoooptions AAPL, calconly iv progress}

{p 5 5 2}
Calculate implied volatilities for already downloaded options data and use bid price (default is ask) of the option in IV calculations.{break}
{cmd: . fetchyahoooptions AAPL, calconly iv progress option_price(Bid)}

{p 5 5 2}
Download options for AAPL for February maturities and calculate implied volatilities (IV){break}
{cmd: . fetchyahoooptions AAPL, m(2014-09-16) iv}

{p 5 5 2}
What the price needs to be in order to break-even after a weekend (maturity is 3 days less), keeping IV constant.{break}
{cmd: . fetchyahoooptions AAPL, m(2014-09-16)}{break}
{cmd: . fetchyahoooptions AAPL, calconly price_target(day) target_day(-3) target_varname("PT_day3")}

{p 5 5 2}
What the price needs to be in order to break-even after a weekend (maturity is 3 days less) and IV increase of 2%{break}
{cmd: . fetchyahoooptions AAPL, m(2014-09-16)}{break}
{cmd: . fetchyahoooptions AAPL, calconly price_target(day iv) target_day(-3) target_iv(+2) target_varname("PT_day3_iv2")}

{p 5 5 2}
What the new ask prices will be after 3 days and IV increase of 2%{break}
{cmd: . fetchyahoooptions AAPL, calconly new_ask(day iv) target_day(-3) target_iv(+2) target_varname("Ask_day3_iv2")}

{p 5 5 2}
What the new ask prices will be after 3 days, IV increase of 2% and price decrease of 1%{break}
{cmd: . fetchyahoooptions AAPL, calconly new_ask(day iv) target_day(-3) target_iv(+2) price_change(-1) target_varname("Ask_day3_iv2_p1")}


{title:Web support}
{p 5 5 2}
{browse "http://papers.ssrn.com/sol3/papers.cfm?abstract_id=2317589":{it:Download via SSRN}.}{break} 
{browse "http://researchbtn.com/?p=114":{it:More examples can be found at www.researchbtn.com}.}

{title:Authors}
{p 5 5 2}
{hi:Mehmet F. Dicle}, Loyola University New Orleans, USA ({hi:mfdicle@gmail.com}){break} 
{browse "http://researchbtn.com":{it:www.researchbtn.com}}
*


fetchyahoooptions.pkg
*
d fetchyahoooptions -- Downloads most recent option prices for a list of symbols from Yahoo! Finance by parsing Yahoo! Finance web page.
d 
d Program by 
d Mehmet F. Dicle, Loyola University New Orleans
d mfdicle@gmail.com
d
d fetchyahoooptions downloads most recent option prices for a list of symbols from Yahoo! Finance by parsing Yahoo! Finance web page.
d 
d
d Created: 10dec2011
d Update: 15aug2016

f fetchyahoooptions.ado
f fetchyahoooptions.hlp
*


stata.toc
*
d
d
p fetchyahoooptions Downloads most recent option prices for a list of symbols from Yahoo! Finance web page.
*



Calculating Financial Statistics and Estimating Multi-Factor CAPM

A new command, fetchportfolio, estimates the multi-factor CAPM and other financial statistics for all the stocks that make up the DJA. CAPM estimations are based on daily percentage change of dividend and split adjusted closing prices. The formula for estimating multi-factor CAPM (Fama (1992) and Fama (1993)) is as follows;
r_{i,t}-r_{f,t}=\alpha+\beta (r_{m,t}-r_{f,t}) + \gamma (SMB_t) + \theta (HML_t) +\epsilon_{t}

Syntax
*
portfolio namelist, year(numlist >0)
*


Description

fetchportfolio estimates and calculates financial statistics to compare financial securities for portfolio selection. fetchyahooquotes is needed for fetchportfolio to run.

Options
  • namelist is a list of ticker symbols for which the statistics are calculated and CAPM is estimated. Symbols are separated by spaces.
  • year(numlist) is a list of years for which the statistics are calculated and CAPM is estimated. Years are separated by spaces.


How to install
*
net from "http://researchbythenumbers.com/stata/010/"
*

Then, click on the fetchportfolio link and then "click here to install".

Example #1: Usage
*
fetchportfolio AAPL IBM XOM, year(2014 2015)
describe
*



Screen Shot 2016-08-20 at 3.44.07 PM
Screen Shot 2016-08-20 at 3.44.20 PM

Interpretting Results
Multi-factor CAPM Beta
*
list Symbol Beta* R2*
*



Screen Shot 2016-08-20 at 3.46.45 PM

In the table above, there are two columns for each year: Beta and R2. The Beta is the Beta estimated with the regression equation: r_{i,t}-r_{f,t}=\alpha+\beta (r_{m,t}-r_{f,t}) + \gamma (SMB_t) + \theta (HML_t) +\epsilon_{t}  . R2 refers to the R-squared for the same regression. CAPM is estimated for each stock separately for each year. Higher Beta (absolute value of the Beta) means higher market risk for each stock. Higher R-squared means more of the variation of daily stock returns is explained by the controlled independent variables: market risk, small-minus-big and high-minus-low. As Beta increases from 2014 to 2015, we can conclude that the stocks' market risk increased. As R-squared increased from 2014 to 2015, we can conclude that the uncontrolled factors (i.e. company specific risks) became less important factors to explain daily returns' variation.

Total return
*
list Symbol Sum_20*
*



Screen Shot 2016-08-20 at 3.50.11 PM

Total return is the percentage change in split and dividend adjusted closing prices from the first day to the last day of the period plus the dividend yield for the year. The figures are in percentage terms (i.e. AAPL's return for 2014 was 44.80\% whereas it was -0.35\% for 2015).

Total risk
*
list Symbol Sd_20*
*



Screen Shot 2016-08-20 at 3.51.59 PM

Total risk is the standard deviation of daily returns. Standard deviation of daily returns are based on daily percentage change of dividend and split adjusted closing prices. It is interpreted as systemic and un-systemic risks combined.

Sharpe measure
*
list Symbol Sharpe_20*
*



Screen Shot 2016-08-20 at 3.52.57 PM

The formula for the Sharpe measure is as follows: Sharpe = (Mean daily return - mean risk-free rate) / Standard deviation of daily returns. Mean daily returns are based on daily percentage change of dividend and split adjusted closing prices. Therefore, higher the ratio, higher the mean return per level of standard deviation (total risk).

Treynor measure
*
list Symbol Treynor_20*
*



Screen Shot 2016-08-20 at 3.54.15 PM

The formula for the Treynor measure is as follows: Treynor = (Mean daily return - mean risk-free rate) / Multifactor CAPM Beta. Similar to Sharpe measure, higher the ratio, higher the mean return for level of Beta (market risk).

fetchportfolio.ado
*
program define fetchportfolio, rclass
	
	version 10.0
	
	syntax anything(name=tickers), year(numlist >0)
	
	qui: {
		local tickers2 :subinstr local tickers "." "_", all
		local tickers2 :subinstr local tickers2 "^" "_", all
		local tickers2 :subinstr local tickers2 "-" "_", all
	
		* Years are entered as a numlist. Thus, the list could be 2000 2001 2010 etc. 
		* When downloading the daly prices from Yahoo Finance, we need the minimum of the list and the maximum of the list. 
		local min_year=9999
		local max_year=0001
		foreach aa in `year' {
			if (`aa'<`min_year') local min_year=`aa'
			if (`aa'>`max_year') local max_year=`aa'
		}

		fetchyahooquotes `tickers', freq(v)   /* downloading the dividend payments */
		foreach aa in `tickers2' { /* tickers2 converst BMW.DE to BMW_DE */
			capture: gen dividends_`aa'=.   /* not every stock pays dividends. create a dividend for those that do not pay that is equal to missing. This is for uniformity. */
		}

		if (substr("`:type date'" , 1, 3) == "str") {
			gen date2=date(date,"YMD")
			drop date
			rename date2 date
			format %td date
		}
		sort date
		
		* Downloading the daily price data along with Fama & French factors, the date range from the January 1st of the minimum year to December 31st of the maximum year.
		noi: fetchyahooquotes `tickers', freq(d) chg(ln per) ff3 merge start("01jan`min_year'") end("31dec`max_year'")  
		foreach aa in `tickers2' { /* tickers2 converst BMW.DE to BMW_DE */
			capture: replace dividends_`aa'=dividends_`aa'/adjclose_`aa'   /* not every security can be downloaded (i.e. insufficient observations) */
		}
		
		local tickers="`r(downloaded)'"
		local howmany :word count `tickers'	/* count the number of tickers entered and downloaded*/
		local tickers2 :subinstr local tickers "." "_", all
		local tickers2 :subinstr local tickers2 "^" "_", all
		local tickers2 :subinstr local tickers2 "-" "_", all
		
		* Changing from calendar days to trading days.
		* Lag of Monday must be Friday, not Sunday
		gen day=_n
		tsset day
		save temp_00001.dta, replace

		// Estimating the CAPM Beta and CAPM R-squared
		foreach aa in `year' {
			use temp_00001.dta, clear
			foreach bb in `tickers2' { /* tickers2 converst BMW.DE to BMW_DE */
				gen per_`bb'_temp=per_`bb'-ff3_RF
				reg per_`bb'_temp ff3_Mkt_RF ff3_SMB ff3_HML if year(date)==`aa'
				local B_`bb'=_b[ff3_Mkt_RF]
				local R2_`bb'=e(r2)
				drop per_`bb'_temp
			}
			clear 
			set obs `howmany'
			gen Symbol=""
			gen Beta_`aa'=.
			gen R2_`aa'=.
			local counter=0
			foreach bb in `tickers' {
				local bba :subinstr local bb "." "_", all
				local bba :subinstr local bba "^" "_", all
				local bba :subinstr local bba "-" "_", all

				local counter=`counter'+1
				replace Symbol="`bb'" if _n==`counter'
				replace Beta_`aa'=`B_`bba'' if _n==`counter'
				replace R2_`aa'=`R2_`bba'' if _n==`counter'
			}
			label variable Beta_`aa' "Multifactor CAPM Beta"
			label variable R2_`aa' "Multifactor CAPM R-squared"
			sort Symbol
			save temp_00001_`aa'.dta, replace
		}

		// Mean, total and standard deviation of returns
		foreach aa in `year' {
			use temp_00001.dta, clear
			foreach bb in `tickers2' {
				summ day if year(date)==`aa'
				
				replace adjclose_`bb' = adjclose_`bb'[`r(max)'-1] in `r(max)' if adjclose_`bb'[`r(max)']==.
				replace adjclose_`bb' = adjclose_`bb'[`r(min)'+1] in `r(min)' if adjclose_`bb'[`r(min)']==.
				
				local son_`bb'= adjclose_`bb'[`r(max)']
				local ilk_`bb'= adjclose_`bb'[`r(min)']
				local sum_`bb'=(`son_`bb''-`ilk_`bb'')/`ilk_`bb''
				
				summ per_`bb' if year(date)==`aa', detail
				local sd_`bb'=r(sd)
				local mean_`bb'=r(mean)
			}
			
			qui: summ ff3_RF if year(date)==`aa', detail
			local mean_ff3_RF=r(mean)
			
			clear 
			set obs `howmany'
			gen Symbol=""
			gen Sum_`aa'=.
			gen Sd_`aa'=.
			gen Mean_`aa'=.
			gen Mean_rf_`aa'=.
			local counter=0
			foreach bb in `tickers' {
				local bba :subinstr local bb "." "_", all
				local bba :subinstr local bba "^" "_", all
				local bba :subinstr local bba "-" "_", all
			
				local counter=`counter'+1
				replace Symbol="`bb'" if _n==`counter'
				replace Sum_`aa'=`sum_`bba''*100 if _n==`counter'
				replace Sd_`aa'=`sd_`bba''*100 if _n==`counter'
				replace Mean_`aa'=`mean_`bba''*100 if _n==`counter'
				replace Mean_rf_`aa'=(`mean_`bba''-`mean_ff3_RF')*100 if _n==`counter'
			}
			label variable Sum_`aa' "Total return for the year (includes dividend yield)."
			label variable Sd_`aa' "Standard deviation of daily returns for the year."
			label variable Mean_`aa' "Average daily return for the year."
			label variable Mean_rf_`aa' "Average daily return for the year minus average risk-free rate."
			sort Symbol
			save temp_00002_`aa'.dta, replace
		}


		* Dividends
		foreach aa in `year' {
			use temp_00001.dta, clear
			foreach bb in `tickers2' {
				qui: summ dividends_`bb' if year(date)==`aa', detail
				local sum_`bb'=r(sum)
			}
			clear 
			set obs `howmany'
			gen Symbol=""
			gen Dividends_`aa'=.
			local counter=0
			foreach bb in `tickers' {
				local bba :subinstr local bb "." "_", all
				local bba :subinstr local bba "^" "_", all
				local bba :subinstr local bba "-" "_", all

				local counter=`counter'+1
				replace Symbol="`bb'" if _n==`counter'
				replace Dividends_`aa'=`sum_`bba''*100 if _n==`counter'
			}
			label variable Dividends_`aa' "Dividend yield for the year."
			sort Symbol
			save temp_00003_`aa'.dta, replace
		}

		erase temp_00001.dta

		local counter=0
		foreach aa in `year' {
			local counter=`counter'+1
			if (`counter'==1) {
				use temp_00001_`aa'.dta, clear
				erase temp_00001_`aa'.dta
			}
			else {
				merge Symbol using temp_00001_`aa'.dta
				erase temp_00001_`aa'.dta
				drop _merge
				sort Symbol
			}
			
			merge Symbol using temp_00002_`aa'.dta
			erase temp_00002_`aa'.dta
			drop _merge
			sort Symbol
			
			merge Symbol using temp_00003_`aa'.dta
			erase temp_00003_`aa'.dta
			drop _merge
			sort Symbol
		}
		
		
		foreach aa in `year' {
			replace Sum_`aa'=Sum_`aa'+Dividends_`aa'
			gen Mean_sd_`aa'=Mean_`aa'/Sd_`aa'
			label variable Mean_sd_`aa' "Mean daily return / Standard deviation of daily returns"
			
			gen Sharpe_`aa' = Mean_rf_`aa'/Sd_`aa'
			label variable Sharpe_`aa' "(Mean daily return - mean risk-free rate) / Standard deviation of daily returns"

			gen Sum_sd_`aa'=Sum_`aa'/Sd_`aa'
			label variable Sum_sd_`aa' "Total return (includes dividend yield) / Standard deviation of daily returns"
			
			gen Mean_Beta_`aa'=Mean_`aa'/Beta_`aa'
			label variable Mean_Beta_`aa' "Mean daily return / Multifactor CAPM Beta"
			
			gen Treynor_`aa'=Mean_rf_`aa'/Beta_`aa'
			label variable Treynor_`aa' "(Mean daily return - mean risk-free rate) / Multifactor CAPM Beta"

			gen Sum_Beta_`aa'=Sum_`aa'/Beta_`aa'
			label variable Sum_Beta_`aa' "Total return (includes dividend yield) / Multifactor CAPM Beta"
		}

	}

end
*


fetchportfolio.hlp
*
{smcl}
{* 20aug2016}{...}
{cmd:help fetchportfolio}
{hline}


{title:Title}

{p2colset 5 22 24 2}{...}
{p2col:{hi: fetchportfolio} {hline 2}} Estimates and calculates financial statistics to compare financial securities for portfolio selection. 
fetchyahooquotes is needed for portfolio to run.{p_end}
{p2colreset}{...}


{title:Syntax}

{p 8 18 2}
{cmdab:fetchportfolio} {it: namelist}{cmd:, year(numlist >0)}

{synoptset 16 tabbed}{...}
{synopthdr}
{synoptline}
{synopt:{opt year}} Analysis year(s). {p_end}


{p2colreset}{...}

{title:Description}

{pstd}

{opt namelist} is a list of ticker symbols for which the statistics are calculated and CAPM is estimated. 
Symbols are separated by spaces. 
{opt year} is a list of years for which the statistics are calculated and CAPM is estimated. 
Years are separated by spaces. 


{title:Example}

{cmd: . fetchportfolio IBM GOOG SPY BND, year(2010/2011)}

{title:Web support}
{p 5 5 2}
{browse "http://papers.ssrn.com/sol3/papers.cfm?abstract_id=2317589":{it:Download via SSRN}.}{break} 
{browse "http://researchbtn.com/?p=114":{it:More examples can be found at www.researchbtn.com}.}

{title:Authors}
{p 5 5 2}
{hi:Mehmet F. Dicle}, Loyola University New Orleans, USA ({hi:mfdicle@gmail.com}){break} 
{browse "http://researchbtn.com":{it:www.researchbtn.com}}
*


fetchportfolio.pkg
*
d fetchportfolio -- Estimates and calculates financial statistics to compare financial securities for portfolio selection.
d 
d Program by 
d Mehmet F. Dicle, Loyola University New Orleans
d
d fetchportfolio estimates and calculates financial statistics to compare financial securities for portfolio selection.
d 
d
d Created: 7dec2011
d Updated: 15aug2016

f fetchportfolio.ado
f fetchportfolio.hlp
*


stata.toc
*
d
d
p fetchportfolio estimates and calculates financial statistics to compare financial securities for portfolio selection.
*