Google Trends mass import using R

I was looking for a tool for mass download of daily Google Trends data for my thesis and couldn't find anything that worked for my needs. The tool was built to download Google Trends data in monthly snippets for  a given search word and given year or years.

I thought I would share my work here in case someone else has stumbled on the same issues. The file can be found here on github: https://gist.github.com/321k/823cce9769e58bc14214.

Make sure you have the quantmod library installed before you run it.

Getting the data

Since the code works by downloading the file thorugh the browser, it is not affected by changes to Google's authentication policy. Simply make sure that you are signed in to your Google account. This does however mean that the data download is slow. I recommend using Firefox with a tab manager to close the tabs after a download has been completed. Tab mix plus 0.4.1.3.1 works great for me. You will also need to check the box in the download prompt that lets Firefox download without prompting you. Finally, you need to specify the download directory as an empty folder.

There are four functions; downloadGT, importGT, formatGT, and mergeGT. downloadGT takes two imputs, the years you want to download, and the search querry you want to get. To run for multiple querries, simply add a loop:

querries=c("MSFT", "AAPL")
years=c("2012", "2013")
for(i in 1:length(querries) {downloadGT(years, querries[i])}

 Formating the data

Once we have downloaded the files, we need to import it to R and put it in a useable format. importGT gets the data, formatGT extracts the time series data, and mergeGT put the individual months into complete time series by company.

path="C:/data"
rawData=importGT(path)
formatedData=formatGT(rawData)
mergedData=mergeGT(formatedData)

And there you have it. I'm currently in the process of downloading the Google Trends data for FTSE 100 between 2004 and 2013. Google's quota limit allows me to download about ten companies per day, or 1200 files. Feel free to leave a comment or suggestion
.

Daily Google Trends data using R

This blog post is a work in progress and has been updated several times. Last update 11.5.2016.

For an explanation of how to combine weekly and daily Google Trends data to create long daily time series with Search Volume Data, please refer to this blog post. You can find a working example in R here.

This R script weaves daily Google Trends data together into a continuous time series. For a look at how daily Google Trends data differs from the weekly data, take a look at this blog post. The graph below illustrated the daily search  data for "FTSE 100".

Since I had a lot of problems with authentication in the available tools for downloading Google Trends data, I decided to circumvent the whole authentication issue by downloading the files through the browser. The file downloads are automated using R. To execute the example below, you will need to add these functions from my Github account to R.

Make sure that you are signed in before you run the script. Since you will download 120 individual csv files, it will take several minutes to complete the run.

The function URL_GT creates the URL for the Google Trends file export. The function readGT downloads the file through the browser and returns the time series data. When the functions are available to R, running the code below shoud return a time series that looks like the one above. Let me know in the comments if it isn't working.


library(Rmisc)
library(ggplot2)
library(dplyr)

# The Google Trends formating functions -----------------------------------

#This script automates the downloading of Google Trends.
#It works best with firefox in combination with the Tab Mix Plus add-on that is used to automate tab closing.
#Ask firefox not to prompt for new downloads and this script should run automatically.
#Google Trends restricts the number of download to roughly 400 at a time.

URL_GT=function(keyword="", country=NA, region=NA, year=NA, month=1, length=3){
  
  start="http://www.google.com/trends/trendsReport?hl=en-US&q="
  end="&cmpt=q&content=1&export=1"
  geo=""
  date=""
  
  #Geographic restrictions
  if(!is.na(country)) {
    geo="&geo="
    geo=paste(geo, country, sep="")
    if(!is.na(region)) geo=paste(geo, "-", region, sep="")
  }
  
  queries=keyword[1]
  if(length(keyword)>1) {
    for(i in 2:length(keyword)){
      queries=paste(queries, "%2C ", keyword[i], sep="")
    }
  }
  
  #Dates
  if(!is.na(year)){
    date="&date="
    date=paste(date, month, "%2F", year, "%20", length, "m", sep="")
  }
  
  URL=paste(start, queries, geo, date, end, sep="")
  URL <- gsub(" ", "%20", URL)
  return(URL)
}

downloadGT=function(URL, downloadDir){
  
  #Determine if download has been completed by comparing the number of files in the download directory to the starting number
  startingFiles=list.files(downloadDir)
  browseURL(URL)
  endingFiles=list.files(downloadDir)
  
  while(length(setdiff(endingFiles,startingFiles))==0) {
    Sys.sleep(3)
    endingFiles=list.files(downloadDir)
  }
  filePath=setdiff(endingFiles,startingFiles)
  return(filePath)
}


readGT=function(filePath){
  rawFiles=list()
  
  for(i in 1:length(filePath)){
    if(length(filePath)==1) rawFiles[[1]]=read.csv(filePath, header=F, blank.lines.skip=F)
    if(length(filePath)>1) rawFiles[[i]]=read.csv(filePath[i], header=F, blank.lines.skip=F)
  }
  
  output=data.frame()
  name=vector()
  
  for(i in 1:length(rawFiles)){
    data=rawFiles[[i]]
    name=as.character(t(data[5,-1]))
    
    #Select the time series
    start=which(data[,1]=="")[1]+3
    stop=which(data[,1]=="")[2]-2
    
    #Skip to next if file is empty
    if(ncol(data)<2) next
    if(is.na(which(data[,1]=="")[2]-2)) next
    
    data=data[start:stop,]
    data[,1]=as.character(data[,1])
    
    #Convert all columns except date column into numeric
    for(j in 2:ncol(data)) data[,j]=as.numeric(as.character(data[,j]))
    
    #FORMAT DATE
    len=nchar(data[1,1])
    
    #Monthly data
    if(len==7) {
      data[,1]=as.Date(paste(data[,1], "-1", sep=""), "%Y-%m-%d")
      data[,1]=sapply(data[,1], seq, length=2, by="1 month")[2,]-1
      data[,1]=as.Date(data[,1], "%Y-%m-%d", origin="1970-01-01")
    }
    
    #Weekly data
    if(len==23){
      data[,1]=sapply(data[,1], substr, start=14, stop=30)
      data[,1]=as.Date(data[,1], "%Y-%m-%d")
    }
    
    #Daily data
    if(len==10) data[,1]=as.Date(data[,1], "%Y-%m-%d")
    
    #Structure into panel data format
    panelData=data[1:2]
    panelData[3]=name[1]
    names(panelData)=c("Date", "SVI", "Keyword")
    if(ncol(data)>2) {
      
      for(j in 3:ncol(data)) {
        appendData=data[c(1,j)]
        appendData[3]=name[j-1]
        names(appendData)=c("Date", "SVI", "Keyword")
        panelData=rbind(panelData, appendData)
      }
    }
    
    #Add file name  
    panelData[ncol(panelData)+1]=filePath[i]
    
    #Add path to filename
    names(panelData)[4]="Path"
    
    #Merge several several files into one
    if(i==1) output=panelData
    if(i>1) output=rbind(output, panelData)
  }
  return(output)
}

readGeoGT=function(filePath){
  output=data.frame()
  rawFiles=list()
  for(i in 1:length(filePath)){
    if(length(filePath)==1) rawFiles[[1]]=read.csv(filePath, header=F, blank.lines.skip=F)
    if(length(filePath)>1) rawFiles[[i]]=read.csv(filePath[i], header=F, blank.lines.skip=F)
  }
  
  for(i in 1:length(rawFiles)){
    data=rawFiles[[i]]
    start=which(data[,1]=="")[3]+3
    stop=which(data[,1]=="")[4]-1
    names=data[start-1,]
    
    for(j in 1:ncol(names)) names(data)[j]=as.character(names[1,j])
    data=data[start:stop,]
    data[,1]=as.character(data[,1])
    data[,-1]=as.numeric(as.character(data[,-1]))
    data[ncol(data)+1]=filePath[i]
    
    output=rbind(output, data)
  }
  return(output)
}


# Downloading the data ----------------------------------------------------


search_terms = c("bull market", "bear market", "recession")

years = c(2005,2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016)
months = c(1,4,7,10)
res.daily=list()
counter=1
for(year in years){
  for(month in months){
    url=URL_GT(search_terms, year=year, month=month)
    GT_dir = downloadGT(url, downloadDir)
    GT_dir = paste(downloadDir, GT_dir, sep='/')
    res.daily[[counter]] = readGT(GT_dir)
    counter=counter+1
  }
}

df.daily <- do.call("rbind", res.daily)

url = URL_GT(search_terms)
GT_dir = downloadGT(url, downloadDir)
GT_dir = paste(downloadDir, GT_dir, sep='/')
df.weekly = readGT(GT_dir)


# Formating the data ------------------------------------------------------


df.merged = merge(df.daily, df.weekly, by=c('Date', 'Keyword'), all.x=T)
df.merged$adjustment_factor = df.merged$SVI.y /df.merged$SVI.x

for(i in search_terms){
  r=which(df.merged$Keyword==i)
  for(j in 2:length(r)){
    if(!is.finite(df.merged$adjustment_factor[r][j])){
      df.merged$adjustment_factor[r][j] = df.merged$adjustment_factor[r][j-1]
    }
  }
}
df.merged$daily = df.merged$adjustment_factor * df.merged$SVI.x
df.merged$weekly = df.merged$SVI.y
for(i in search_terms){
  r=which(df.merged$Keyword==i)
  for(j in 2:length(r)){
    if(is.na(df.merged$weekly[r][j])){
      df.merged$weekly[r][j] = df.merged$weekly[r][j-1]
    }
  }
}


# Plotting the data -------------------------------------------------------

df.merged$daily[which(is.infinite(df.merged$daily))] = NA

p1 = df.merged %>%
  ggplot(aes(Date, daily, color=Keyword))+geom_line()

p2 = df.merged %>%
  ggplot(aes(Date, weekly, color=Keyword))+geom_line()

multiplot(p1,p2)


# Saving the data ---------------------------------------------------------


write.csv(df.merged,'df.merged.csv')

Earnings multiples aren't driven by growth alone

A high earnings multiple is commonly used to classify a stock as a growth stock (just look at what Jim Cramer from Mad Money writes). Growth is not the whole story. By looking at the key value driver formula, we can see that the earnings multiple is determined by growth and the return on invested capital (ROIC). Divide both sides of the formula with the net operating profit less adjusted taxes (NOPLAT) to get the earnings multiple.

• Value = (NOPLAT(1-g/ROIC))/(WACC-g)
• Divide both sides with NOPLAT
• Value/NOPLAT = (1-g/ROIC)/(WACC-g)
• As we can see, the earnings multiple on the left is a function of both growth and ROIC

The Key Value Driver Formula and the Zen of Corporate Finance

This post is a part of my series on valuation. Today, I look at the fundamental drivers of value in a business.


When valuing a company, there are two main factors to consider. The Return on Invested Capital (ROIC) and the growth in cash flows (g)

• Growth rate = return on new invested capital * investment rate
• ROIC = capital invested in the business = PPE + net working capital (typically)

If the growth remains constant in perpetuity, we can use the following formula to calculate the value of a company:

• Value = Free Cash Flow,t=1/(Cost of Capital - Growth)

The cost of capital can be substituted for the Weighted Average Cost of Capital (WACC)

Example: 

Earnings = 100
Net investment = 25
Cash Flow,t=1 = 75
Cost of Capital = 10%
Growth = 5%

Value = 75/(10%-5%) = 75/0.05 = 1500 

Based on this valuation, we can also calculate an implied earnings multiple:

• Earnings multiple = Valuation/Earnings = 1500/100 = 15X

The free cash flow is calculated as NOPLAT minus net investment. NOPLAT is Net Operating Profit Less Adjusted Taxes and represents the cash generated by the business in a given year. From this we can create what is called the key value driver formula:

• Value = (NOPLAT,t=1 * (1 - g / WACC)) / (WACC - g)

This article is based on the book Valuation by Koller, Goedhart & Wessel. They call the key value driver formula the Zen of Corporate Finance "because it relates a company's value to the fundamental drivers of economic value: growth , ROIC and the cost of capital".

Currently reading: Great Minds in Finance: The Efficient Market Hypothesis

I'm reading the fourth book in Colin Read's series Great Minds in Finance that describes the people and discoveries behind the Efficient Market Hypothesis. Since I'm an avid chess player myself, this quote stood out:

To better understand the meaning of a fair game of the type Bachelier modeled, contrast the analogy with chess and the card game poker. A game of chess can be resumed at any time by any player without disadvantage. All observers of a snapshot of the chess board have all the available information. The past history of moves and captures is immaterial. The only relevant issue is the position of the chess men at any moment. However, in poker, even if one could observe all hands at a time t or have all inside information belonging to each player at a given stage in the game, past history remains important. The composition of the pile of cards that have been discarded and swapped are only partially known, and only to individual players. And even if one were to know the cards they discarded, and hence had som idea of the cards remaining in the deck or in the hands of others, the information is imperfect. In fact, poker is an imperfect game of insiders information while chess or coin tossing are games that are informationally perfect, as Bachelier and the proponents of efficient markets have assumed of financial markets ever since.

So chess, not poker, would be the best analogy for an efficient market.

Bitcoins are the first asset class driven solely by attention

I downloaded Bitcoin price data for 1.4.2012-31.3.2013 and assembled daily Google searches for Bitcoin for the same time period. This is the scatterplot of the two. 90% of the variation in the Bitcoin price is explained by search volume on Google Trends, which is pretty impressive.

Research on the correlation between Google Trends data and stock prices have found correlations between volatility and searches. But to see this direct relationship between actual prices and searches is pretty stunning.

Does this mean that an increase in searches indicates a price increase? It might of course very well be the other way around, that increasing Bitcoin prices lead to more attention.

How to get daily Google Trends data for more than 90 days (Excel)

Note: This post describes how to combine Google Trends series using Excel. If you wish to use R, please read this article.

Google Trends is a great source for insight, but the tool can be a bit limiting. For instance, it is only possible to download daily search data three months at a time. Google Trends will give you weekly data for up to a year at a time, and after that only monthly data. Since the data is provided in an index format, we need to adjust the independent indexes. So how can we merge the quarterly time series into a yearly time series with daily data?

The three graphs show the problem of merging quarterly data directly. By using the weekly data as a benchmark we can create an accurate index with daily data.

Step 1 

Download the data in 90-day increments. In this example I assume that we want a whole year worth of daily data, that means four csv files to merge.

• Report (1).csv: January - March 2012 
• Report (2).csv: April - June 2012 
• Report (3).csv: July - September 2012 
• Report (4).csv: October - December 2012 

Step 2 

Download the weekly data for the same year.

• Report (5).csv: January - December 2012 

Step 3 

Merge the daily data files into one Excel spreadsheet. Leave a gap in between each quarter so you remember where the adjustment must be made.

Step 4 

This is where we need to adjust the indexes based on the weekly data file we downloaded. We will do this based on the weekly time series data for the whole year we also downloaded. At the start of each quarter, write the corresponding index value from the weekly data. Then calculate the percentage change between the days and add that to the new index value. Repeat for each quarter and you have created a new yearly index with daily data.