What made Google’s stock tumble? Did Dow Jones have a role? University Edition can help solve this case!
What’s the data?
Today, we continue the Free Data Friday series with a look at some stock prices from 2014 for Google and Dow Jones. Do these entities – one a wildly successful search engine, the other a stock market index, influence each other’s stock value? Let’s run their data through University Edition and find out.
Get University Edition
If you don’t already have University Edition, get it here. If you download and use it through virtualization software, follow closely the appropriate Quick Start Guide (a pdf on the download page.) If you need help with almost any aspect of using University Edition, check out these video tutorials.
How to download
I like that you don’t have to download any data directly and pull from your computer. University Edition allows you to pull from the url. Click the links above and making sure your dates encompass all of 2014. (Jan. 1t, 2014 – Dec. 31t, 2014). After getting the prices, scroll to the bottom of the table, right click the download to spreadsheet link and copy the link address.
Making data analytics ready
Note that in the filename statement there is a url statement as well. This allows you to call a url rather than a file in SAS Studio. The piece in quotes is the url link that you copied from the website earlier. Since we have the input statement, we won’t need the first row, which is only variable names, so cut them out with the firstobs option. Make sure you include the format on the date variable. Run a quick sort on both datasets by date so that you can merge the two datasets together by date. The merge starts the same as any other data step, but instead of using a set statement or an infile statement, you use a merge statement. Then list all the datasets you want to merge together.
Be sure to include the new_date with the input function so that you can adjust the variable to a date value. In your final data step, format the new_date variable and drop the old date. The retain statement brings it back to the front since all new variables are added to the end of the dataset by default.
filename Google url 'http://real-chart.finance.yahoo.com/table.csv?s=GOOGL&a=00&b=1&c=2014&d=11&e=31&f=2014&g=d&ignore=.csv'; filename DowJones url 'http://real-chart.finance.yahoo.com/table.csv?s=DIA&a=00&b=1&c=2014&d=11&e=31&f=2014&g=d&ignore=.csv'; data Google_Stock_Data; infile Google dlm="," firstobs= 2; input date $char10. Goo_Open Goo_High Goo_Low Goo_Close Goo_Volume Goo_Adj_Close ; run; data DowJones_Stock_Data; infile dowjones dlm="," firstobs= 2; input date $char10. Dow_Open Dow_High Dow_Low Dow_Close Dow_Volume Dow_Adj_Close ; run; proc sort data= google_stock_data; by date; run; proc sort data= dowjones_stock_data; by date; run; data merge_stocks; merge google_stock_data dowjones_stock_data; by date; New_Date= input(trim(date), yymmdd10.); run; data merge_stocks_date; retain new_date; set merge_stocks; format New_Date date10.; drop date; run; proc corr data= merge_stocks; var Goo_close dow_close; run;
What does the output mean?
The correlation shows us a moderate negative correlation. However, looking at the two plotted against each other you can see that there was a significant drop in Google’s price. This is a bit curious. Upon further research, it appears Google split its stock on April 3, 2014. This stock was then turned into two separate stocks and this one was lowered to about half of its previous value.
The internet is full of explanations of what happened. To save you a keystroke, here’s an article on the Wall Street Journal’s MoneyBeat blog.
Now it’s your turn!
Did you find something else interesting in this data? Share in the comments.
Need data for learning?
The SAS Communities Library has a growing supply of free data sources that you can use in your training to become a data scientist. The easiest way to find articles about data sources is to type "Data for learning" in the communities site search field like so:
We publish all articles about free data sources under the Analytics U label in the SAS Communities Library. Want email notifications when we add new content? Subscribe to the Analytics U label by selecting SAS Communities Library at the bottom of the right nav:
Click Analytics U, then select "Subscribe" from the Options menu.