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).
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.
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.
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.
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;
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).
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.
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:
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.
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.
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.
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"?
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".
Trading financial instruments, including foreign exchange on margin, carries a high level of risk and is not suitable for all investors. The high degree of leverage can work against you as well as for you. Before deciding to invest in financial instruments or foreign exchange you should carefully consider your investment objectives, level of experience, and risk appetite. The possibility exists that you could sustain a loss of some or all of your initial investment and therefore you should not invest money that you cannot afford to lose. You should be aware of all the risks associated with trading and seek advice from an independent financial advisor if you have any doubts.
2
