Analyzing Financial Data Using R and the Quandl Package

Hello. This article combines the topics of finance, statistics and programming. I have been playing around the Quandl package in R for analysis of financial stock price data.

What Is Quandl?

Quandl is a platform which has many databases storing financial and econometrics data. The website makes it easier for data analysts in the financial industry to acquire and analyze data.

Example of financial data in Quandl include stock data, futures data, currency data, interest data, and options data.

Millions of datasets are available for free (with a user account) and more datasets are available for premium users.

The tools supported by Quandl include R, Python, Excel, C, C++, Ruby, SAS, Stata, Java and more.

Importing Financial Data Into R Using Quandl

Before importing data into Quandl, one needs to obtain an API key in order to have access. To obtain an API key, an account needs to be created. A new account can be created for free.

To start, we load the Quandl package and the ggplot2 package in R:

Importing Data

To import data from Quandl, you need a Quandl access code. This access code can be found on the top right of the webpage.

I am using the Quandl code “YAHOO/MSFT” to analyze Microsoft stock data from Yahoo Finance. Here is a link containing the Quandl code.

We take a sneek peek at our Microsoft data. The code and output is below.

From the output we see that dim(microsoft) gives us an output of 7660 rows /observations and 7 columns/variables. The head() and tail functions shows us the first six (by default) and the last six rows of the dataset respectively. The summary() function gives a summary of each variable in the dataset with minimums, maximums, median, mean and the 1st and 3rd quartiles.

We also see that the stock data of Microsoft is from March 13, 1986 to July 29, 2016.

Analyzing The Stock Price Data of Microsoft

Before we start the analysis, we produce a plot in R using the ggplot2 package.

We now have an idea how the historical stock prices of Microsoft were. One can see that Microsoft’s stock price from 2005 to 2015 is not as high compared to the 1995 to 2000 period.

We can gather some basic information/statistics about our stock. The code and output is below.

The average closing stock price from the March 13, 1986 to July 29, 2016 period is \$59.25.

The lowest closing stock price (in bold) is \$15.15 on the date of March 9, 2009.

On March 25, 1999 Microsoft has its highest closing stock price at \$179.94.

Microsoft has its highest volume of 1,031,788,800 on March 13, 1986 (start of the data and when Microsoft went public with its Initial Public Offering / IPO).

Changing the Time Period

What if we want more recent data of the Microsoft stock such as from 2000 to July 29, 2016? Something as far as before 2000 does not carry much weight compared to more recent data.

We will now consider the Microsoft stock data from January 1, 2000 to July 29, 2016. The output and code is below.

The code above is pretty much the same as before. We added the start_date and the end_date arguments to create a smaller time window of the data.

We now produce a plot and find some basic information about the stock from 2000 to July 29, 2016.

We can see from the plot that Microsoft’s stock has not been too good from 2000 to 2005. The stock is increasing steadily towards its 2000 stock price from 2005 to July 29, 2016.

The lowest closing stock price (in bold) is \$15.15 on the date of March 9, 2009.

The highest closing stock price in this period is \$116.56 which is lower than the highest  closing price of \$179.94 in March 25, 1999.

On April 28, 2006, Microsoft’s highest volume in the 21st century is 591,052,200.

Notes and Thoughts

• The analysis provided is based on a larger time window of Microsoft’s stock. If one wants to conduct more (statistical) analyses and predictions topics such as correlations, time series analysis and other econometrics methods will be needed.
• Some say that the past has no affect on the present. There is truth to that in certain cases. The plots do show some trends over time.
• Remember that statistics is based on partial information. The plots do contain a lot of information but it needs to be supplemented by context. The context here is business competition and other economic forces.
• From a non-statistics view, Microsoft now has Google and Apple as competitors. Google and Apple were not as big before 2000 as they are today. I think there is a very low chance (less than 1%) that Microsoft can reach its highest closing stock price of \$179.94 in March 25, 1999 anytime in the next 3 years.

References

1. https://www.quandl.com/data/YAHOO/MSFT-MSFT-Microsoft-Corporation