After i got the list of all tickers of products around the world, I started pulling data and doing some analysis. The easiest of the analysis were
1) Finding the spread
2) Finding the correlated products
I chose stocks from 2 countries
a) BSE Stocks from India
b) German Stocks from the DE Bourse
c) Currency data all over the world
Here are a few interesting points
From the analysis from the year 2013 onwards, the currencies CAD and GTQ had the highest negative correlation. In order to verify it, I pulled data from the website and it matches
On the flip side, the highly correlated currencies were DKK and XAF
One more observation is that most of the OPEC countries have their currency highly correlated to the dollar trying to find a correlation between them and other currencies s similar to finding correlations between USD and other currencies
The R script which i used to find out these is located here and here. The first file is to calculate the spread/deviation and the second is to download the data. The scripts are pretty simple, one needs to change the directories though for their use
If you want to use excel to extract the stock information, then the best method would be YQL .It is easy to use and you have all sorts of information in a structured format
If you want to pull out historical prices with the following specifciations
Symbol/Ticker : symbol
StartDate
EndDate
then the correpsonding weblink to extract this info would be ( the below is in excel vba format )
"http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20in%20%28%27 " & symbol & "%27%29%20and%20startDate%20=%20%27" & startDate & "%27%20and%20endDate%20=%20%27" & endDate & "%27&diagnostics=true&env=store://datatables.org/alltableswithkeys"
I have created an excel sheet where you can give a list of the stock tickers and the start and end date and it will download the neccessary information within the excel sheet
You can download the sheet here and tweak it as per your requirements. Enjoy
1) Finding the spread
2) Finding the correlated products
I chose stocks from 2 countries
a) BSE Stocks from India
b) German Stocks from the DE Bourse
c) Currency data all over the world
Here are a few interesting points
On the flip side, the highly correlated currencies were DKK and XAF
One more observation is that most of the OPEC countries have their currency highly correlated to the dollar trying to find a correlation between them and other currencies s similar to finding correlations between USD and other currencies
The R script which i used to find out these is located here and here. The first file is to calculate the spread/deviation and the second is to download the data. The scripts are pretty simple, one needs to change the directories though for their use
If you want to use excel to extract the stock information, then the best method would be YQL .It is easy to use and you have all sorts of information in a structured format
If you want to pull out historical prices with the following specifciations
Symbol/Ticker : symbol
StartDate
EndDate
then the correpsonding weblink to extract this info would be ( the below is in excel vba format )
"http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20in%20%28%27 " & symbol & "%27%29%20and%20startDate%20=%20%27" & startDate & "%27%20and%20endDate%20=%20%27" & endDate & "%27&diagnostics=true&env=store://datatables.org/alltableswithkeys"
I have created an excel sheet where you can give a list of the stock tickers and the start and end date and it will download the neccessary information within the excel sheet
You can download the sheet here and tweak it as per your requirements. Enjoy
No comments:
Post a Comment