TLDR: https://docs.google.com/spreadsheets/d/1tIRHkmU2xn_88A3X3ohWW70E268r4Fe3TpFI8rYIQlY/edit
For many researchers, collecting, aggregating, and cleaning data is a time consuming process. Hiring undergrad research assistants can help, but the full process has significant overhead. This past semester, I worked on a stock market event study for my applied data analysis class in Wharton (BEPP 280). During the data collection process, using Bloomberg and other school resources to repeatedly download stock prices was very frustrating. Every time we wanted to modify our dataset, there was significant overhead to collecting the stock price information.
Since we were working in Google Drive, I looked into alternative ways to pull information into our Google Sheets. As it turns out, there is a GOOGLEFINANCE function that will pull historical stock price information. We immediately began using this to pull stock prices for the given estimation/event window in our study on a stock by stock basis. Unfortunately, this still meant copy-pasting formulas (since Google's fill methods are not as sophisticated as excel's yet). Out of convience, the copy-paste solution was what I used to create the final dataset.
After we had completed the dataset and most of the analysis, I looked to automating the dataset creation process. My previous research and work experience involved some Google Apps Scripts work which gave me a strong baseline to start from. I began converting the copy-paste formulas to Apps Scripts formulas until the entire process was automated. Once that was complete, I added variable inputs to allow for varying estimation windows and event windows.
You can find the google sheet template Here. Make a copy and begin adding tickers with event dates!
Note this is only functional for events between 1999 and 2019. Make sure your stock ticker is valid for the given window, or you may have blank rows.
function getPrices() {
//Get user input sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName("Input");
var outputSheet = ss.getSheetByName("Data");
outputSheet.clear();
var inputs = inputSheet.getRange("G2:G4").getValues();
var length = inputs[0][0];
var estimationWindow = inputs[1][0];
var observationWindow = inputs[2][0];
var prePeriod = estimationWindow + observationWindow + 1;
var postPeriod = observationWindow + 1;
var inputRange = inputSheet.getRange(6, 1, length, 4)
//Populate Template DataSheet with formulas
var inputValues = inputRange.getValues();
var outputLength = length * (estimationWindow + observationWindow);
// Crete headers at top of output sheet
var headers = new Array (8);
headers[0] = "Stock Name";
headers[1] = "Event Date";
headers[2] = "Date";
headers[3] = "Close";
headers[4] = "S&P Close";
headers[5] = "Stock Daily Return";
headers[6] = "S&P Daily Return";
headers[7] = "Estimation Window";
outputSheet.appendRow(headers);
for (var i = 0; i < length; i++) {
// Initialize all formulas/values used in current iteration
var ticker = inputValues[i][0];
var name = inputValues[i][1];
var eventDate = new Date(inputValues[i][3]);
var formattedDate = Utilities.formatDate(eventDate, "America/New_York", "MM/dd/YYYY");
var dateFormula = "=query(GOOGLEFINANCE(\"%s\",\"price\", WORKDAY.INTL(\"%s\",-%d,1, Holidays!$A$2:$A),WORKDAY.INTL(\"%s\",%d,1,Holidays!$A$2:$A)),\"select * label Col1 '', Col2''\")";
var formattedDateFormula = Utilities.formatString(dateFormula, ticker, formattedDate, prePeriod, formattedDate, postPeriod);
var snpFormula = "=query(GOOGLEFINANCE(\".INX\",\"price\",INDIRECT(\"R[0]C[-2]\", FALSE)),\"select Col2 label Col2 ''\")"
var stockRtn = "=if(INDIRECT(\"R[0]C[-5]\", FALSE)=INDIRECT(\"R[-1]C[-5]\", FALSE),(INDIRECT(\"R[0]C[-2]\", FALSE)-INDIRECT(\"R[-1]C[-2]\", FALSE))/INDIRECT(\"R[-1]C[-2]\", FALSE),\"\")";
var snpRtn = "=if(INDIRECT(\"R[0]C[-6]\", FALSE)=INDIRECT(\"R[-1]C[-6]\", FALSE),(INDIRECT(\"R[0]C[-2]\", FALSE)-INDIRECT(\"R[-1]C[-2]\", FALSE))/INDIRECT(\"R[-1]C[-2]\", FALSE),\"\")";
var combinedSize = prePeriod+postPeriod;
// Create template for current stock
var newLine = new Array (5);
newLine[0] = ticker;
newLine[1] = formattedDate;
newLine[2] = formattedDateFormula;
newLine[3] = "";
newLine[4] = snpFormula;
outputSheet.appendRow(newLine);
// Add non-queried information line by line
for (var j = 0; j < combinedSize; j++) {
var formulas = [
[name, formattedDate]
];
var rowRange = outputSheet.getRange(combinedSize * i+j+2, 1, 1, formulas[0].length);
rowRange.setValues(formulas);
var estimationDummy = j <= estimationWindow ? 1 : 0;
var formulas1 = [
[snpFormula, stockRtn, snpRtn, estimationDummy]
];
var rowRange = outputSheet.getRange(combinedSize * i+j+2, 5, 1, formulas1[0].length);
rowRange.setFormulas(formulas1);
}
}
// Sleep to allow data to load from GOOGLEFINANCE
Utilities.sleep(1000 * length);
var sheet = outputSheet;
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
// Convert all formulas to static values to avoid errors
rows.copyValuesToRange(sheet, 1, sheet.getLastColumn(), 1, sheet.getLastRow())
// Delete all rows with intentionally blank returns
var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[5] == "" || row[5] == '') {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
}