Looping code over multiple excel sheets

[Anton Jannerberg]

Hello, I am new to Stata and trying to loop code to calculate Idiosyncratic risk for companies in separate Excel sheets. This is the code I want to loop for each sheet in my excel workbook. All sheets are named after the company which information it contains. I realise it should not be particularly difficult but being new to Stata, I simply cannot figure out how to make it work.

import excel myfilepath/myfilename.xlsx, sheet("my_sheetname") firstrow

// Estimate the beta using the CAPM
regress Stockreturn Marketreturn
estimates store beta_coeff // store the beta coefficient

// Specify the beta coefficient
scalar beta = _b[Marketreturn]

// Calculate the expected return using the CAPM
summ Marketreturn
scalar Marketreturn_mean = r(mean)
scalar expected_return = 0.03 + beta * (Marketreturn_mean - 0.03)

// Calculate the idiosyncratic risk using the CAPM
summarize Stockreturn
scalar Stockreturn_var = r(Var)
scalar idiosyncratic_risk = Stockreturn_var - beta^2 * (Marketreturn_mean - 0.03)^2

// Display the results
display "Beta: " beta
display "Expected return: " expected_return
display "Idiosyncratic risk: " idiosyncratic_risk

Thank you in advance
Anton

[Emil Alnor]

The Stata command you might be looking for is -foreach. See Cox, N. J. (2020). Speaking Stata: Loops, again and again. The Stata Journal, 20(4), 999–1015. https://doi.org/10.1177/1536867X20976340 for an introduction. I cannot provide you the exact code to solve your problem, as you did not provide they names of the companies, but what might would work is something like:

foreach company in company1 company2 company3 company4{
import excel myfilepath/`company'.xlsx, sheet("my_sheetname") firstrow

// Estimate the beta using the CAPM
regress Stockreturn Marketreturn
estimates store beta_coeff_`company' // store the beta coefficient

// Specify the beta coefficient
scalar beta = _b[Marketreturn]

// Calculate the expected return using the CAPM
summ Marketreturn
scalar Marketreturn_mean = r(mean)
scalar expected_return = 0.03 + beta * (Marketreturn_mean - 0.03)

// Calculate the idiosyncratic risk using the CAPM
summarize Stockreturn
scalar Stockreturn_var = r(Var)
scalar idiosyncratic_risk = Stockreturn_var - beta^2 * (Marketreturn_mean - 0.03)^2

// Display the results
display "Beta: " beta
display "Expected return: " expected_return
display "Idiosyncratic risk: " idiosyncratic_risk
}

Note that I added `company' some places. This is what Stata will replace with your company names when looping over the list.

However, if the variables are the same for all the companies, I think an eaiser solution to your problem would be to gather all excel-files in one dataset using -append, and then calculating your statistics for each company.