3.1 Data set structures

In time-series econometrics there are basically the following dataset structures or types:

1. Time-series: in this structure you can have many periods, and information for one “subject” (subject can be company, index, industry, etc). Also, you can have more than one subject, but the information is placed as new column for each subject. For example: the dataset created after running getsymbols:

##        p_stock1 r_stock1 p_stock2 r_stock2
## 2014m1       10     0.02       20     0.01
## 2014m2       11     0.10       21     0.05

2. Cross-sectional structure: in this structure, you usually have many “subjects”, for ONLY ONE period For example, key performance indicators of Mexican companies for the last quarter of 2016:

##     Ticker   ROA ATO ProfitMargin
## 1 ALFAA.MX 0.023 0.9         0.15
## 2  AMXL.MX 0.015 1.1         0.10

3. Panel-data structure: it is combination of time-series and cross-sectional. In this structure, we have more than one subject, and for each subject we can have more than one period. Example:

##      Ticker quarter  ROA ATO ProfitMargin
## 1  ALFAA.MX  2014q1  0.2 1.1         0.17
## 2  ALFAA.MX  2014q2 0.21 1.2         0.16
## 3       ...     ...  ... ...          ...
## 4 BIMBOA.MX  2014q1 0.15 0.8         0.10
## 5 BIMBOA.MX  2014q2 0.20 0.9         0.05
## 6       ...     ...  ... ...          ...
## 7 BIMBOA.MX  2017q1 0.15 1.1         0.09

3.2 Introduction to Data management

In Econometrics, data management skills are very important. Most of the time, before designing and running an econometric model, it is needed to do simple and sophisticated data management. It is very common to merge data sets before we start our econometric analysis.

In this example we will learn how to collapse a dataset and merge one dataset with another considering the data structures we learned in the previous section.

In this case, you have to create a dataset with quarterly information for the IPyC market index using monthly data. Then, you have to merge this dataset with a panel-dataset of financial information for many Mexican stocks. In more detail, you have to do the following:

Download the dataset: http://www.apradie.com/datos/datamx2020q4.xlsx using the readxl package (you have to install this package). This dataset has real quarterly financial data of Mexican rms (from the BMV) for many years. This information comes from Economatica, a leading software and database economic and financial information. This dataset is a panel dataset, which is a combination of cross sectional and time-series dataset. Navigate through the data (using View() function) to learn more about this dataset. Save this dataset with a name.

# Load the package
library(readxl)
# Download the excel file from a web site:
download.file("http://www.apradie.com/datos/datamx2020q4.xlsx",
              "dataw6.xlsx", mode="wb")
# The first parameter is the link and the second is a name for the
#  local file

# Use the function read_excel()
dataset <- read_excel("dataw6.xlsx")

We need to attach the market return to my dataset. Then, I need to:

1. Download the monthly market index from Yahoo Finance (getsymbols)

2. Transform (Collapse) the dataset from monthly to quarterly

Now download the ipyc:

library(quantmod)
getSymbols("^MXX", from="2000-01-01", to= "2019-12-31",
            periodicity="monthly", src="yahoo")

## [1] "^MXX"

We need to aggregate (collapse) the dataset from monthly to quarterly, keeping the LAST market index per quarter. We can use the to.quarterly function from quantmod:

QMXX <- to.quarterly(MXX,indexAt='startof')

This function creates an OHLC xts dataset for each quarter. We can have a look of the content:

head(QMXX)

##            MXX.Open MXX.High MXX.Low MXX.Close MXX.Volume MXX.Adjusted
## 2000-01-01  7185.71  8417.33 6510.84   7473.25          0      7473.25
## 2000-04-01  7459.75  7639.38 5516.77   6948.33          0      6948.33
## 2000-07-01  6953.73  7548.57 6171.95   6334.56          0      6334.56
## 2000-10-01  6334.64  6502.60 5148.02   5652.19          0      5652.19
## 2001-01-01  5651.35  6615.83 5512.77   5727.89          0      5727.89
## 2001-04-01  5733.67  6895.70 5541.68   6666.17 4888424600      6666.17

We see that the Open, High, Low, Close and Adjusted prices for each quarter were calculated. We only need the Adjusted price to calculate the market returns, so we select only the Close column:

QMXX = QMXX$MXX.Adjusted
# Change the name of the column:
colnames(QMXX) <- "MXXindex"

Now we can calculate cc return of the market

QMXX$MXXreturns <- diff(log(QMXX)) 

Now we are almost ready to merge this quarterly data to the panel dataset using merge.

We need to have both datasets with a common column. In this case, the common column is quarter. Both datasets must be of the same class, in this case, data frame class.

The QMXX has the quarter as index, but not as part of a column. We create a data frame with the quarter column equal to its index:

# Create a dataframe with a quarter column for the QMXX
QMXX.df<-data.frame(quarter=index(QMXX),coredata(QMXX))
# I extracted the quarter from the index
# coredata is a function that extract the data of an object
#   without extracting formatting

The common column must be of the same type, so we make sure that the column quarter of dataset is a Date type:

dataset$quarter<-as.Date(dataset$quarter)

Now we do a MANY-TO-ONE merge of both datasets:

dataset<-merge(dataset,QMXX.df,by="quarter")
# Now I have a dataset for all firms-quarters, and the
# MXX index and MXX return is merged for each firm

Now we define the dataset as a panel data using the pdata.frame function. You need to install the package “plm”.

library(plm)
datasetp <- pdata.frame(dataset, index= c("firmcode","quarter"))

I need to tell R that the dataset is panel data with the structure firm-quarter.

Before running multiple regressions with this panel dataset I need some data cleanning.

3.3 Data cleaning - Extreme values

When one or more independent variables of a regression model have very extreme values, the regression coefficients are usually not reliable. The extreme values of a variable are called outliers. The OLS method used for regression is very sensible for outliers. In other words, when you have outliers in an independent variable, your regression coefficients can be biased and not reliable.

In this section we continue using the same data set. You have to do the following:

Learn about the Book-to-market ratio (BMR). As comments, EXPLAIN what the BMR is.

Calculate the BMR for all firm-quarters of the dataset. Consider book value of equity as total assets minus total liabilities. Calculate market value of the fir according to the stock price and the number of shares outstanding:

datasetp$bookvalue= datasetp$totalassets - datasetp$totalliabilities
datasetp$marketvalue =  datasetp$originalhistoricalstockprice*datasetp$sharesoutstanding

Check that I used original stock price, not adjusted stock price. In financial markets, the adjusted stock prices are calculated after considering dividend payments and stock splits. A stock split is when a firm decides to divide the value of its stock price by 2, 3 or other multiple with the only purpose to avoid the perception that the stock is expensive. For example, late August 2020 Apple and Tesla decided to do stock split. Apple did a split on a 4-for-1 basis. This means that if the stock price was about USD$400.00 on that day, then its price was reduced to USD$100.00, but they multiplied the number of shares (shares outstanding) by 4 to keep the same market value of the firm. In this historical dataset the shares outstanding is the historical, so we need to use the historical/original stock price without considering stock splits nor dividend payments.

We calculated book value and market value for ALL rms and ALL quarters! Now we can generate the book-to-market ratio for all firms all quarters:

datasetp$bmr= datasetp$bookvalue / datasetp$marketvalue

If bmr=1 means that the firm book value is about the same as firm market value. If that is the case, then the market value has not grown beyond book-value, meaning that the firm has not created value beyond its book value.

If bmr>1 means that the market value is less than book value. So, if bmr>1 means that the firm has significantly lost shareholder’s wealth, and it might incur in bankrupt risk.

Then, what would be the bmr level that all firms are looking for? One of the main purposes of the executives is to MAXIMIZE shareholder’s value. The way to increase shareholder’s value is to increase its market value, and the only way to increase market value is to increase stock price.

Then, the bmr level that all executives prefer is a value much less than 1.

If bmr=0.5 means that the firm market value is the double of its book value. In this case, the main difference between market value and book value is the wealth that the firm has created thanks to its valuable intangible assets such as prestige, high quality, and innovation.

Then, what do you think it might be the relationship between bmr and stock return? Intuitively, we might think that a small value of bmr is a good news, then the stock return might be related in a negative way. If bmr goes down (good news), then the stock return might go up. Then, it might be expected that the relationship between bmr and stock return is linear and negative. Some finance research (like Fama & French, 1995), mainly with US firms has found that the relationship between bmr and future stock return is negative.

However, there are mixed findings about the relationship between bmr and stock returns. Some research (like Piotrosky, 2000) has found that firms with high bmr, but strong financials (like earnings per share) usually provides significant positive stock returns. In this special case, bmr has found to be positively related to stock returns.

Finance research has found that bmr influences earnings per share, which in turn influences current and future stock returns. It is some times that firms with low bmr do not experience significant high stock returns due to the high expectations of investors. This is an interesting finding in finance that is still being researched!

In the following models we will see what happens for Mexican firms.

1. Keep ONLY active firms in the dataset. You can do this by typing:

datasetpa<-datasetp[datasetp$status=="active",]

As you see, we can do sub-setting or selection of rows in a dataset indicating a condition as the row index, and putting nothing in the column index (after the coma)

We do this since the cancelled firms usually have non reliable information and very extreme values.

2. Run the histogram command to see the distribution of bmr.

hist(datasetpa$bmr, main="Histogram of the bmr data", 
     xlab="bmr", col="dark blue")

3. Plot the outliers to identify the observations for bmr. You can type

boxplot(datasetpa$bmr)

outlier_values <- boxplot.stats(datasetpa$bmr)$out 

4. Install the package robustHD, which has the winsorize function that performs the winsorization process. With your words EXPLAIN what is winsorization.

library(robustHD)

5. Apply the winsor command to BMR at the 2 percentile level. Comment what you did with the winsor command. Type the following:

winsbmr <- winsorize(na.omit(datasetpa$bmr), standarized= "True", prob= 0.98, return= "data")

What is winsorization?

Winsorizing is the process where the non-missing values of a variable (x) are ordered

    x_1 <= ... <= x_n

and generate a new variable y identical to x except that the h highest and h lowest values 
are replaced by the next value counting inwards from the extremes:

    y_1, ... , y_h = y_(h + 1)

    y_n, ... , y_(n - h + 1) = y_(n - h)
    

This process is applied to variables with extreme values.

For high values= upper percentile is calculated and that value is assigned to the top values located in a percentile less than the assigned.

For low values= a lower percentile is calculated and that value is assigned to the bottom values that are in a percentile lower than the assigned value.

In this case we winsorized bmr for high and low extreme values.

Now we look at a cleaner bmr data, we can see this graphically making a histogram of the new winsorized data:

hist(winsbmr, main="Histogram of the winsorized bmr data", 
     xlab="bmr", col="dark blue")

As expected, most of the BMR values are less than 1 for active firms in the BMR, indicating that market value is greater than book value for most of the active firms most of the time.

Once you winsorize BMR, you can safely use it in a regression model.