Stata tip: Importing Stock info from Yahoo! Finance and FED macroeconomic data

07Feb10

When implementing CAPM factor models you need time series on stock quotes and macroeconomic data, you can import both kind of variables directly in Stata with two wonderful packages.

The first one is freduse:
One can use findit command to locate the ADO and install it:

findit freduse
freduse GDP

//You will be able to find the name of other macro variables in http://research.stlouisfed.org/fred2/

If you happen to find an error like:

r(3499) _fredifinparse() 

just run the following code in Stata 10

mata: mata mlib index

The second package is stockquote:
stockquote ^GDAXI, fm(1) fd(2) fy(2000) lm(03) ld(4) ly(2008) frequency(d)
//example from help file by Nikos Askitas.
findit stockquote

Below a simple .do file to create a complete dataset with stocks prices and macro data in monthly frequence, note that GDP is quarterly and I use Denton method to interpolate it to monthly data.

clear
set mem 100m
tempfile finance

foreach var in YHOO TOSBF.PK AAPL MSFT GOOG ^GSPC ^NDX {
local newname0=ltrim(subinstr(“`var’”,”.”,”",.))
local newname=ltrim(subinstr(“`newname0′”,”^”,”",.))
tempfile newname
display “`newname’”
stockquote `var’, fm(1) fd(1) fy(1996) lm(12) ld(1) ly(2009) frequency(m)
generate t=date( date,”YMD”)
drop date
format t %td
tsset t

global all “open-adjclose”
foreach v of varlist $all {
local newname0=ltrim(subinstr(“`var’”,”.”,”",.))
local newname=ltrim(subinstr(“`newname0′”,”^”,”",.))
rename `v’ `newname’`v’
}
sort t
local newname0=ltrim(subinstr(“`var’”,”.”,”",.))
local newname=ltrim(subinstr(“`newname0′”,”^”,”",.))
tempfile `newname’
save ““newname””
}

foreach var in YHOO TOSBF.PK AAPL MSFT GOOG ^GSPC {
local newname0=ltrim(subinstr(“`var’”,”.”,”",.))
local newname=ltrim(subinstr(“`newname0′”,”^”,”",.))
merge t using ““newname””, unique sort
rename _merge mrg`newname’
tsset t
sort t

}

gen t2=mofd(t)
format t2 %tm
duplicates drop t2, force
sort t2
save “`finance’”

tempfile macro
capture clear
freduse CPIAUCNS GDP EMRATIO CAINFON
gen year= year( daten)
keep if year>=1996
gen t2=mofd(daten)
format t2 %tm
sort t2
save “`macro’”

use “`finance’”, clear
merge t2 using “`macro’”, unique sort
drop mrg*
drop t
drop date
drop _merge
label variable t2 “date”
set scheme sj
egen quarter = repeat(), v(1/4) block(3)

drop year
gen year= year( daten)

bys year quarter: egen GDPb=median(GDP)

bys year quarter: egen seq=seq(), from(1) to (3)

gen GDPc=GDPb
replace GDPc=. if seq==2 | seq==1

dentonmq GDPc, ind(t2) gen(GDPdenton)

drop GDPc seq

//DENTON METHOD TO INTERPOLATE SERIES FROM QUARTERLY TO MONTHLY
//http://www.imf.org/external/pubs/ft/qna/2000/Textbook/ch6.pdf
compress
save “$bases\factorCAPM.dta”, replace

[\sourcecode]