Data source Quandl - Trading Software

Data source Quandl

0 1 - Next

Zoidberg
2014-01-19 00:27:07

Hi,
I discovered a potentially great new data source, "Quandl", which I wanted to share with the community.
Is it well-known?
If anyone have experience with it (or not), feel free to make a comment!

It seems daily quotes might be lagging by a few days, which seems reasonable since the data are curated and free, so might not replace other data sources,
but seems they have a lot of interesting economics/social timeseries in addition to financial.

The web-interface, at least, is still in beta, and doesn't support Internet Explorer (well, kind of works, but after a "bug-report"
I was asked to use Firefox or Chrome).

Regards,
Zoidberg

Links:

Homepage: http://www.quandl.com/
http://en.wikipedia.org/wiki/Quandl
https://twitter.com/quandl
http://gigaom.com/2013/05/31/its-a-beautiful-thing-when-free-data-meets-free-analytics/
(Another interesting link from previous link: https://bigml.com/)
http://blog.revolutionanalytics.com/2013/02/quandl-a-wikipedia-for-time-series-data.html
http://www.journalism.co.uk/news/quandl-aims-to-be-the-youtube-for-data-/s2/a555280/
http://www.waterstechnology.com/inside-market-data/news/2319139/quandl-bows-crowd-sourced-data-curation
http://www.r-bloggers.com/evaluating-quandl-data-quality/
http://datamining.typepad.com/data_mining/2013/12/quandl-the-data-engine.html

clonex
2014-01-19 02:04:58

Futures data have not quality for backtesting IMO. There is a downloader which uses data from quandl http://www.quantshare.com/item-1269-eod-data-for-eurodollar-futures

it is easy to modify script for other futures

Zoidberg
2014-01-19 02:50:08

Might be.
A link on more or less the same topic (repeated from first post): http://www.r-bloggers.com/evaluating-quandl-data-quality/

Zoidberg
2014-01-19 03:06:20

And another one: http://www.thertrader.com/2013/11/15/evaluating-quandl-data-quality/
Same analysis as in previous link it seems, but more discussion at end, with feedback from Quandl.

See also part II: http://www.thertrader.com/2013/12/02/evaluating-quandl-data-quality-part-ii/

Thanks for the downloader tip!

Jim Harrison
2014-01-19 06:51:58

Zoid and Clonez,

Been using it for a while, several conversations with the founder following a few "database crashes" recently. I like it and think they are here for while at least. They are partnered with a few online testing, strategy platforms I am familiar with.

I have installed and used the quandl R package, it is very efficient.

A few points and thoughts:

1. Anyone remember opentick? Need I say more. I hate investing time into "free" data consolidation providers just to have them vanish.
2. Quandl seems to be a bit more committed to the long term, so that is encouraging.
3. I have used them for:

a. EOD futures continuous contract data. See link below for the futures I trade most actively via Qshare.

b. FRED economic data, like money supply, etc.

4. The downloaders I built were simple, and took less than 5 minutes. Not sure how to upload or I would share them here as well. See below.
5. I like the superset feature: http://www.quandl.com/USER_4S9-Accuquant/62D-Untitled-Superset-2013-12-27-22-01-06

Conclusion: Yes, it is a solid treasure trove of useful info great for getting those historical series, but not a primary source for critical daily updates just yet, especially following the recent "database crashes". I do not think it has any advantages over the Google or Yahoo downloaders for EOD stock data in most cases, and has tested to be much slower than yahoo.

Spreadsheet for import showing both Interactive Brokers Symbols and Quandl Symbols.

https://docs.google.com/spreadsheet/pub?key=0AgEdxHeBlouUdHFaOTljcl83VWwxYWZWQ2ZvNG5fZWc&output=html

Example of URL string for downloader:
http://www.quandl.com/api/v1/datasets/[SYMBOL].csv?&auth_token=XXXXXXXXXXXXXXXXXXXXX&trim_start=[2Y]-[2M2]-[2D2]&trim_end=[Y]-[M2]-[D2]

Note: Most Econ series and "other" series like M2 Money Supply (FRED/BASE) only have DATE and VALUE, so make sure to check the parser to align VALUE with CLOSE for ease of use, else all the data ends up in the open field. I ended up with three variations to accommodate my needs.

Jim Harrison
2014-01-19 07:09:58

Attempted to upload a downloader for all. We will see if I succeeded?

Maybe someone(AZ?) can contribute a post script to deal with the ever present NaN or Zero values?

Enjoy if so...

QuantShare
2014-01-19 22:13:03

Here is the downloader uploaded by Jim: Quandl.com Universal Downloader

We will add a post script to handle the NaN and Zero values.

Quandl.com Universal Downloader (by Jim Harrison, uploaded several months ago)

No notes

clonex
2014-01-20 00:40:55

Good Work !!!

Zoidberg
2014-01-20 02:03:58

Excellent, thanks a lot!
I was trying to roll my own last night, but this looks way better.

Jim Harrison
2014-01-25 08:03:49

Postscript help:

for(int i=0;i<Data.Rows.Length;i++)
{
PostScriptRow row = Data.Rows[i];
if((double)row.Data[2] == 0 ||
(double)row.Data[3] == 0 ||
(double)row.Data[4] == 0)
{
((double)row.Data[2] == (double)row.Data[5] &&
(double)row.Data[3] == (double)row.Data[5] &&
(double)row.Data[4] == (double)row.Data[5]));
//row.IsIgnoreLine = true;
}
}

I am trying to set columns 2,3& 4 to == column5 if they are zero... Missing a little something here.... Any help?

QuantShare
2014-01-25 13:01:33

Columns starts with index = 0

for(int i=0;i<Data.Rows.Length;i++)
{
PostScriptRow row = Data.Rows[i];
if((double)row.Data[1] == 0 || (double)row.Data[2] == 0 || (double)row.Data[3] == 0)
{
row.Data[1] = (double)row.Data[5];
row.Data[2] = (double)row.Data[5];
row.Data[3] = (double)row.Data[5];
}
}

Zoidberg
2014-01-25 14:00:45

Hi,
I think there is another point to make (correct me if I am wrong):
As far as I tested, the value "0" (maybe as opposed to 0.0?)becomes a "boxed" int (Object containing int), not double, and unboxing through "(double)" does not work
(and will give an error message at Runtime, and I guess the script terminates at that point).
Maybe that should be changed so numerical values are always double?

Here is an attempt to fix that.
I was a bit uncertain exactly which columns You wanted Jim, considering that coumns start at 0 and also that the date is already lopped off, so change the value of "off" so it suits you.

for (int i=0;i<Data.Rows.Length;i++)
{
PostScriptRow row = Data.Rows[i];
int off = -2;

double val1 = (double)Convert.ChangeType(row.Data[2+off], typeof(double));
double val2 = (double)Convert.ChangeType(row.Data[3+off], typeof(double));
double val3 = (double)Convert.ChangeType(row.Data[4+off], typeof(double));

if (val1 == 0 || val2 == 0 || val3 == 0)
{
row.Data[2+off] = row.Data[5+off];
row.Data[3+off] = row.Data[5+off];
row.Data[4+off] = row.Data[5+off];
}
}

Jim Harrison
2014-01-25 21:27:29

Thanks for the quick responses...

I send my most sincere thanks to both. Je vous adresse mes plus vifs remerciements.

As you suspected, the first fix caused error for column0,

Here is what I came up with, seeing that I really needed no offset since I wanted columns 0,1,2 to == 3. Thanks AZ && Zoid for the lesson.

for (int i=0;i<Data.Rows.Length;i++)
{
PostScriptRow row = Data.Rows[i];

double val1 = (double)Convert.ChangeType(row.Data[0], typeof(double));
double val2 = (double)Convert.ChangeType(row.Data[1], typeof(double));
double val3 = (double)Convert.ChangeType(row.Data[2], typeof(double));

if (val1 == 0 || val2 == 0 || val3 == 0)
{
row.Data[0] = row.Data[3];
row.Data[1] = row.Data[3];
row.Data[2] = row.Data[3];
}
}

Anyway, I added this iteration to the downloader, and uploaded.

Am also putting together a few interesting "symbol lists" to further leverage the Quandl datasets. I look to share soon...

God Bless you!

Quandl.com Universal Downloader (by Jim Harrison, uploaded several months ago)

No notes

Zoidberg
2014-01-26 03:01:18

Good work, Jim!
I downloaded and added a few "features" in my copy.
If you want, feel free to try/test/include/ignore any or all of them.

1. Added field "auth_token" of type "nothing", to have an easy place to put authentication.

2. The following pre-script to allow me to download also series with only <datetime> <value>:
PreScriptRow[] rows = Content.Rows;
int N = rows.Length;
for (int irow = 0; irow < N; ++irow) {
string[] data = rows[irow].Data;
if (data[2] == "") {
// Input format <datetime> <value>, but number of fields is still full 7, with fields empty I guess.
// We ignore the current line and add new where we duplicate the value to OHLC and set volume and openint to zero.
rows[irow].IsIgnoreLine = true;
string[] new_row = {data[0], data[1], data[1], data[1], data[1], "0.0", "0.0"};
Content.AddRow(new_row);
}
}

3. The following slightly changed post-script, to divide volumes of indices by 1000, similar to e.g. "Historical Stock Market Data", for consistency and to avoid overrun of integer value used to store volume. The check for index or not is perhaps a little expansive, and not fully tested, and explicitly checks also Name3 which is a bad thing if e.g. you plan to change to Name2 (code change required).

bool do_divide_volume = Data.SymbolInfo.Name.StartsWith("^") // Adapted from "Historical Stock Market Data" and C# doc.
|| Data.SymbolInfo.Group.Equals("Indices", StringComparison.OrdinalIgnoreCase)
|| Data.SymbolInfo.Name3.IndexOf("INDEX_", StringComparison.OrdinalIgnoreCase) >= 0;

for (int i=0;i<Data.Rows.Length;i++)
{
PostScriptRow row = Data.Rows[i];

double val1 = (double)Convert.ChangeType(row.Data[0], typeof(double));
double val2 = (double)Convert.ChangeType(row.Data[1], typeof(double));
double val3 = (double)Convert.ChangeType(row.Data[2], typeof(double));

if (val1 == 0 || val2 == 0 || val3 == 0)
{
row.Data[0] = row.Data[3];
row.Data[1] = row.Data[3];
row.Data[2] = row.Data[3];
}

if (do_divide_volume)
row.Data[4] = (double)Convert.ChangeType(row.Data[4], typeof(double)) / 1000.0;
}

Jim Harrison
2014-01-26 07:47:27

Thanks for the help, only a fool would ignore sage counsel. I try not to be a fool when possible ;)

1. Great idea on the placeholder for the auth token.

2. Sweet! Let me chew on this one and see if I can fully understand how awesome it is ;) Ok I got it, Say goodbye to ALL of the disparate EOD data downloaders.

3. Nice thinking, just got my "^" symbols ported over today manually. Didnt realize I had that many....

I definitely will add all 3.

I was naturally thinking about a global script? that would concatenate the Name fields with certain other known or introduced strings to populate the name3 field automatically.

OPTION 1:

if(exchange== NASD, name3="GOOG/NASDAQ_".name)

else if(exchange == NYSE, name3="GOOG/NYSE_".name)
else if(name == *^*, name 3 =.............
else ignore

And I as I keep thinking about this one....

Might we do it all in pre-script or maybe URL script and never have to populate any field, just merge the name field (typically the common symbol) with the Qcode based on exchange?

I imagine we would lose speed, as the time to create the URL's will be added to the download process rather than a static db check?

Anyway, the thought is we could just add what it needs to create the Qcode in pre script? or URL script? and use the name field.... on the fly... per symbol.....

so for each symbol, we check on the fly the following to create the [SYMBOL] for the download URL:

if(exchange== NASD, [SYMBOL]="GOOG/NASDAQ_".name)

else if(exchange == NYSE, symbol="GOOG/NYSE_".name)
else if(name == *^*, symbol = etc, etc, etc....

else ignore and error message,

This way, if the unthinkable were to happen like Qcodes change or whatever, simply update the script not your entire database.

Anyway, just thinking out loud....God Bless You!

Zoidberg
2014-01-27 00:29:28

Hi Jim,
1. Excellent idea to automatically map symbols! However, difficult to make perfectly in a general downloader.
Anyway I had a go. I put the logic into an URL script "quandl_urlscript.cs".
Note: Not considered to be final, rather as a first possible step/for discussion.
Available here: https://www.dropbox.com/sh/5qsf0ewk0wa90ab/cE9hZhpmtP
If you want to try, then also:
a) Rename Your "name" field ("QQ", was it?) to "name",
b) add "auth_token" as mentioned before
c) add field "default_provider", type "Nothing", and make its value "Google" or "Yahoo"

It is a pity there are not more name-fields, since I use name3 for Netfonds-names.
See the code for further comments.

2. I discovered that in at least one symbol, BITCOIN/MTGOXEUR, where 1.7e308 (or thereabouts) was used as "no data" (?). I updated the post-script accordingly (+some pretty-printing etc), otherwise it failed. "quandl_postscript.cs" at https://www.dropbox.com/sh/5qsf0ewk0wa90ab/cE9hZhpmtP

3. Question: Should we use Adjusted close instead of Close, in general?

QS, if you are reading: It seems like Functions.GetEndDate() returns the start-date, and Functions.GetStartDate() the end date.

Regards,
Zoidberg

Zoidberg
2014-01-27 00:53:21

Oops, 1 d): Change "Symbol" from "~Name3" to "~Name" in "Update..." window. The script still looks at also name3.
1 e) Delete the URL (but not the URL line) in same window, i. e. empty url-string.
And I wouldn't worry about speed until we see any problem.

- Zoidberg

Jim Harrison
2014-01-27 10:49:08

Wow, super!

I like what I see so far, need to do some more testing on a few weird datasets but so far so good. I think this about covers most scenarios.

1. No problems w/ URL script. Clear instructions are easy to follow verbatim to reproduce results. Thanks for taking the time to articulate!

2. I have seen that in some of the currencies as well, thanks for the education... Nice fix in the post script, including the volume adjustment for the indices.

3. Depends on purpose: Adjusted Close takes into account splits, dividends reinvested, etc. So if you are trying to compare a bond fund to a stock fund, or a non dividend paying stock portfolio to a dividend paying stock you would need adjusted close data to get a true comparison of overall returns when invested in these assets for your fund. This is because a large portion of earnings will come from dividends rather than price appreciation. Some funds take dividends in cash, others re-invest them so really depends on your funds directives and/or personal application. Unless you know or are being told that need need otherwise, I am pretty sure adjusted price is what you are after. Hope this helps!

Still thinking through the "exchange mapping" give me a few days to digest fully and see how I apply it during the week in real time.

More to come for sure, God Bless You!

Zoidberg
2014-01-27 20:13:49

Hi Jim,
glad you like it (so far :-) )

3. I am not sure if this applies generally, but from the few checks I made
a) Quandle Google data has OHLCV (only)
b) Yahoo has OHLCVc (c = adjusted close)
c) Open financial data Project: OHLCVo (o = Open Interest)

Currently the downloader assumes OHLCVo, so
a) Google: Open Interest becomes zero (default) it seems, OK.
b) Yahoo: Close => Close, adjusted close truncated (or rounded) to integer and put in open interest field, not ideal.
c) Open financial data Project, fits downloader, OK.

I think the Yahoo case needs to be handled better, maybe having an option to use adjusted close or close. Open interest should be set to zero since not available.

You probably have a better overview than me of the column-variations that is found in the Quandl-data, does the desctiption above cover it?

Any opinion on the way to handle Yahoo?

Any other major providers available in Quandl, for OHLCV*?
As an aside: Would be nice if they would include Netfonds data (if not restricted data). Netfonds does have a lot of data available, and at least some of it better than Yahoo is my impression.
Imagine a triple-downloader only needing to get data from Quandl, where Google, Yahoo and Netfonds where downloaded together and the median value was chosen (possible future work).

Also: Maybe add an option of using either of "name1", "name2" or "name3"?

Zoidberg
2014-01-27 22:03:33

Hi,
I changed files so that there is an option "use_adjusted_close" (yes/true/1 + uppercase/mix accepted as positive answer), which is used for Yahoo only.
Also Open Interest set to 0 for Yahoo, and some prettyprinting code. New prescript maybe a bit more efficient.

On the negative side:
a) use_adjusted_close is read in the new URL script and assigned to a "global" variable read by the prescript.
What happens if several downloaders are run concurrently with different values?
b) I haven't tested much.

If you want to have a look,
download all files (post-script might not have changed, though) from https://www.dropbox.com/sh/976kw9en2k7za5v/eP1bWAkX-y
and add field "use_adjusted_close" With value e.g. "yes".

Regards,
Zoidberg

Get more messages

No more messages

0 1 - Next

Back


Trading Items Consumer Price Index - Historical data Splits Calendar - Historical data for U.S. Stocks Historical Earnings Surprise, EPS and Consensus Data Market Cap Historical Data for the US Stock Market Implied Volatility of iPath ETNs - IV Historical Data	How-to Lessons How to speed up watchlist and screener plug-ins when working with... How to import trading data from CSV files How to export trading data to a CSV file How to create trading rules based on Put and Call volume data How to display Forex economic calendar data in a chart
Related Forum Threads Daily data source for today's bar? Data Source Combining Symbol Data How do I Import Tick Data from a CVS file Intraday data with volume	Blog Posts Different Ticker Symbols for Each Data Source 6 New Ways to Download Free Intraday Data for the U.S. Stock Mark... 6 ways to download free intraday and tick data for the U.S. stock... How to Download Trading Data for Certain Securities Only Speed Up Optimizations by Saving Ranking Data into a Custom Datab...