MoRe: Data Handling and Other Useful Things {#MoreDataHandling}

Getting started

To get started, we need to grab some data. Go to Yahoo! Finance and download some historical data in a spreadsheet or csv file. Read the file into R as follows:

We can use pipes with the "magrittr" package to do the same thing as follows:

Data Extraction of stocks using the quantmod package

In this chapter, we will revisit some of the topics considered in the previous chapters, and demonstrate alternate programming approaches in R. There are some extremely powerful packages in R that allow sql-like operations on data sets, making for advanced data handling. One of the most time-consuming activities in data analytics is cleaning and arranging data, and here we will show examples of many tools available for that purpose. Let's assume we have a good working knowledge of R by now. Here we revisit some more packages, functions, and data structures.

We have seen the package already in the previous chapter. Now, we proceed to use it to get some initial data.

Are they all the same? Here we need to extract the ticker symbol without quotes.

Now we can examine the number of observations in each ticker.

We see that they are all the same.

Convert closing adjusted prices of all stocks into individual data.frames.

First, we create a list of data.frames. This will also illustrate how useful lists are because we store data.frames in lists. Notice how we also add a new column to each data.frame so that the dates column may later be used as an index to join the individual stock data.frames into one composite data.frame.

Using the merge function

Data frames a very much like spreadsheets or tables, but they are also a lot like databases. Some sort of happy medium. If you want to join two dataframes, it is the same a joining two databases. For this R has the merge function. It is best illustrated with an example.

Make a single data frame

Second, we combine all the stocks adjusted closing prices into a single data.frame using a join, excluding all dates for which all stocks do not have data. The main function used here is merge which could be an intersect join or a union join. The default is the intersect join.

Note that the stock table contains the number of rows of the stock index, which had fewer observations than the individual stocks. So since this is an intersect join, some rows have been dropped.

Plot the stock series

Plot all stocks in a single data.frame using ggplot2, which is more advanced than the basic plot function. We use the basic plot function first.

Convert the data into returns

These are continuously compounded returns, or log returns.

Descriptive statistics

The data.frame of returns can be used to present the descriptive statistics of returns.

Correlation matrix

Now we compute the correlation matrix of returns.

Correlogram

Show the correlogram for the six return series. This is a useful way to visualize the relationship between all variables in the data set.

Market regression

To see the relation between the stocks and the index, run a regression of each of the five stocks on the index returns.

The $\beta$s indicate the level of systematic risk for each stock. We notice that all the betas are positive, and highly significant. But they are not close to unity, in fact all are lower. This is evidence of misspecification that may arise from the fact that the stocks are in the tech sector and better explanatory power would come from an index that was more relevant to the technology sector.

Return versus systematic risk

In order to assess whether in the cross-section, there is a relation between average returns and the systematic risk or $\beta$ of a stock, run a regression of the five average returns on the five betas from the regression.

We see indeed, that there is an unexpected negative relation between $\beta$ and the return levels. This may be on account of the particular small sample we used for illustration here, however, we note that the CAPM (Capital Asset Pricing Model) dictate that we see a positive relation between stock returns and a firm's systematic risk level.

Extracting online corporate data

Suppose we have a list of ticker symbols and we want to generate a dataframe with more details on these tickers, especially their sector and the full name of the company. Let's look at the input list of tickers. Suppose I have them in a file called tickers.csv where the delimiter is the colon sign. We read this in as follows.

The line of code reads in the file and this gives us two columns of data. We can look at the top of the file (first 6 rows).

Note that the ticker symbols relate to stocks from different exchanges, in this case Nasdaq and NYSE. The file may also contain AMEX listed stocks.

The second line of code below counts the number of input tickers, and the third line of code renames the columns of the dataframe. We need to call the column of ticker symbols as ``Symbol'' because we will see that the dataframe with which we will merge this one also has a column with the same name. This column becomes the index on which the two dataframes are matched and joined.

Get all stock symbols from exchanges

Next, we read in lists of all stocks on Nasdaq, NYSE, and AMEX as follows:

If this does not work, use the following URL download of CSV files

Then read in these csv files, after renaming them to nyse_names.csv, amex_names.csv, nasdaq_names.csv

We can look at the top of the Nasdaq file.

Next we merge all three dataframes for each of the exchanges into one data frame.

To see how many rows are there in this merged file, we check dimensions.

Finally, use the merge function to combine the ticker symbols file with the exchanges data to extend the tickers file to include the information from the exchanges file.

An alternate package to download stock tickers en masse is BatchGetSymbols.

Using the DT package

The Data Table package is a very good way to examine tabular data through an R-driven user interface.

Web scraping

Now suppose we want to find the CEOs of these 98 companies. There is no one file with compay CEO listings freely available for download. However, sites like Google Finance have a page for each stock and mention the CEOs name on the page. By writing R code to scrape the data off these pages one by one, we can extract these CEO names and augment the tickers dataframe. The code for this is simple in R.

The code uses the stringr package so that string handling is simplified. Scraping is done with the rvest package. The lines of code using rvest are above. After extracting the page, we search for the line in which the words "Chief Executive Officer" pr "CEO" show up, and we note that the name of the CEO appears in a table in the html page. A sample web page for Apple Inc is shown here: