cancel
Showing results for 
Search instead for 
Did you mean: 

q/kdb+ api for getting market and financial data from IEX

Himanshu
New Contributor II
Figured I would post this here as many of you might find this useful.

This weekend, I wrote a library to get IEX data in kdb. You can get all sorts of data including EOD summary (high, low, open, close, vwap), earnings (actual vs consensus EPS), financials and news. 

Code: https://github.com/himoacs/iex_q
Examples: http://www.enlistq.com/qkdb-api-getting-market-financial-data-iex/

P.S. Through this project, I learnt that q has an operator for parsing json messages which I wasn't expecting. I just wish it had a more descriptive name than -29!. 🙂
13 REPLIES 13

effbiae
New Contributor
thanks Himanshu,

json support is available in .j

nice work on the iex library.

ag1
New Contributor
What is the best way to "unnest" t?
I would like a 4 column table with cols: `sym`mcap`pe`ebitda

q)t:.j.k .Q.hg`$"https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,googl,amzn,fb&types=quote,stats&filter=marketCap,peRatio,EBITDA"

q)

q)t

     | quote                                  stats                 

---- | -------------------------------------------------------------

AAPL | `marketCap`peRatio!8.983506e+11 19.88  (,`EBITDA)!,7.6569e+10

GOOGL| `marketCap`peRatio!7.340723e+11 35.37  (,`EBITDA)!,3.2714e+10

AMZN | `marketCap`peRatio!5.715004e+11 301.02 (,`EBITDA)!,1.4021e+10

FB   | `marketCap`peRatio!5.311237e+11 35.22  (,`EBITDA)!,2.0304e+10

q)

q)type t

99h

Here’s one way to do it:

 

q)`sym`mcap`pe`ebitda xcol ([] sym:key t),'exec (quote,'stats) from value t

sym   mcap         pe     ebitda

------------------------------------

AAPL  8.983506e+11 19.88  7.6569e+10

GOOGL 7.340723e+11 35.37  3.2714e+10

AMZN  5.715004e+11 301.02 1.4021e+10

FB    5.311237e+11 35.22  2.0304e+10

 

Hope this helps

Jonathon

 

From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of ag
Sent: 27 November 2017 08:14
To: Kdb+ Personal Developers <personal-kdbplus@googlegroups.com>
Subject: [personal kdb+] Re: q/kdb+ api for getting market and financial data from IEX

 

What is the best way to "unnest" t?

I would like a 4 column table with cols: `sym`mcap`pe`ebitda

 

q)t:.j.k .Q.hg`$"https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,googl,amzn,fb&types=quote,stats&filte..."

q)

q)t

     | quote                                  stats                 

---- | -------------------------------------------------------------

AAPL | `marketCap`peRatio!8.983506e+11 19.88  (,`EBITDA)!,7.6569e+10

GOOGL| `marketCap`peRatio!7.340723e+11 35.37  (,`EBITDA)!,3.2714e+10

AMZN | `marketCap`peRatio!5.715004e+11 301.02 (,`EBITDA)!,1.4021e+10

FB   | `marketCap`peRatio!5.311237e+11 35.22  (,`EBITDA)!,2.0304e+10

q)

q)type t

99h

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.


This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of AquaQ Analytics may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.

Amazing! This is great. I am reading through the exec documentation but there isn't a full description, just examples.
It seems to pull out the contents from the dictionary key - is that correct?
http://code.kx.com/q/ref/qsql/#exec

q)foo: flip`a`b`c!flip(0 0 0;1 2 3;2 4 6)

q)foo

a b c

-----

0 0 0

1 2 3

2 4 6

q)exec a from value foo

'type

  [0]  exec a from value foo

                   ^

q)exec a from foo

0 1 2




([] sym:key t),'exec quote from value t


On Monday, November 27, 2017 at 3:40:38 AM UTC-8, Jonathon McMurray wrote:

Here’s one way to do it:

 

q)`sym`mcap`pe`ebitda xcol ([] sym:key t),'exec (quote,'stats) from value t

sym   mcap         pe     ebitda

------------------------------------

AAPL  8.983506e+11 19.88  7.6569e+10

GOOGL 7.340723e+11 35.37  3.2714e+10

AMZN  5.715004e+11 301.02 1.4021e+10

FB    5.311237e+11 35.22  2.0304e+10

 

Hope this helps

Jonathon

 

From: personal...@googlegroups.com [mailto:personal...@googlegroups.com]On Behalf Of ag
Sent: 27 November 2017 08:14
To: Kdb+ Personal Developers <personal...@googlegroups.com>
Subject: [personal kdb+] Re: q/kdb+ api for getting market and financial data from IEX

 

What is the best way to "unnest" t?

I would like a 4 column table with cols: `sym`mcap`pe`ebitda

 

q)t:.j.k .Q.hg`$"https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,googl,amzn,fb&types=quote,stats&filte..."

q)

q)t

     | quote                                 stats                 

---- | -------------------------------------------------------------

AAPL | `marketCap`peRatio!8.983506e+11 19.88 (,`EBITDA)!,7.6569e+10

GOOGL| `marketCap`peRatio!7.340723e+11 35.37 (,`EBITDA)!,3.2714e+10

AMZN | `marketCap`peRatio!5.715004e+11 301.02 (,`EBITDA)!,1.4021e+10

FB   | `marketCap`peRatio!5.311237e+11 35.22 (,`EBITDA)!,2.0304e+10

q)

q)type t

99h

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email topersonal-kdbpl...@googlegroups.com.
To post to this group, send email to persona...@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.


This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of AquaQ Analytics may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.


So basically the object "t" from your previous example (the result of JSON parsing by .j.k) is a dictionary where the "values" are dictionaries - and remember, in KDB a list of dictionaries is a table:

q)value t
quote                                  stats
-------------------------------------------------------------
`marketCap`peRatio!8.938324e+11 19.78  (,`EBITDA)!,7.6569e+10
`marketCap`peRatio!7.448348e+11 35.89  (,`EBITDA)!,3.2714e+10
`marketCap`peRatio!5.762372e+11 303.51 (,`EBITDA)!,1.4021e+10
`marketCap`peRatio!5.318502e+11 35.27  (,`EBITDA)!,2.0304e+10


So this is what the "exec" statement in my example is operating on. exec extracts columns from a table - if you exec a single column, the return will be a list. In my previous example, I use join-each (,') to combine the two dictionary columns and exec them as one - therefore the return is a list. As these columns are dictionary columns, when we exec this list, we get a list of dictionaries, i.e. a table

q)exec (quote,'stats) from value t
marketCap    peRatio EBITDA
-------------------------------
8.938324e+11 19.78   7.6569e+10
7.448348e+11 35.89   3.2714e+10
5.762372e+11 303.51  1.4021e+10
5.318502e+11 35.27   2.0304e+10

>From here it's fairly trivial to use join-each to combine this with a table containing the sym values, and xcol is used finally to rename the columns as you requested

Hope that helps
Jonathon



From: personal-kdbplus@googlegroups.com <personal-kdbplus@googlegroups.com> on behalf of ag <anuj.goyal@gmail.com>
Sent: 27 November 2017 17:19
To: Kdb+ Personal Developers
Subject: Re: [personal kdb+] Re: q/kdb+ api for getting market and financial data from IEX
 
Amazing! This is great. I am reading through the exec documentation but there isn't a full description, just examples.
It seems to pull out the contents from the dictionary key - is that correct?
http://code.kx.com/q/ref/qsql/#exec

q)foo: flip`a`b`c!flip(0 0 0;1 2 3;2 4 6)

q)foo

a b c

-----

0 0 0

1 2 3

2 4 6

q)exec a from value foo

'type

 [0]  exec a from value foo

                  ^

q)exec a from foo

0 1 2




([] sym:key t),'exec quote from value t


On Monday, November 27, 2017 at 3:40:38 AM UTC-8, Jonathon McMurray wrote:

Here�s one way to do it:

 

q)`sym`mcap`pe`ebitda xcol ([] sym:key t),'exec (quote,'stats) from value t

sym   mcap         pe     ebitda

------------------------------------

AAPL  8.983506e+11 19.88  7.6569e+10

GOOGL 7.340723e+11 35.37  3.2714e+10

AMZN  5.715004e+11 301.02 1.4021e+10

FB    5.311237e+11 35.22  2.0304e+10

 

Hope this helps

Jonathon

 

From: personal...@googlegroups.com [mailto:personal...@googlegroups.com]On Behalf Of ag
Sent: 27 November 2017 08:14
To: Kdb+ Personal Developers <personal...@googlegroups.com>
Subject: [personal kdb+] Re: q/kdb+ api for getting market and financial data from IEX

 

What is the best way to "unnest" t?

I would like a 4 column table with cols: `sym`mcap`pe`ebitda

 

q)t:.j.k .Q.hg`$"https://api.iextrading.com/1.0/stock/market/batch?symbols=aapl,googl,amzn,fb&types=quote,stats&filte..."

q)

q)t

     | quote                                  stats                 

---- | -------------------------------------------------------------

AAPL | `marketCap`peRatio!8.983506e+11 19.88  (,`EBITDA)!,7.6569e+10

GOOGL| `marketCap`peRatio!7.340723e+11 35.37  (,`EBITDA)!,3.2714e+10

AMZN | `marketCap`peRatio!5.715004e+11 301.02 (,`EBITDA)!,1.4021e+10

FB   | `marketCap`peRatio!5.311237e+11 35.22 (,`EBITDA)!,2.0304e+10

q)

q)type t

99h

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email topersonal-kdbpl...@googlegroups.com.
To post to this group, send email to persona...@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.


This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of AquaQ Analytics may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email topersonal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

ACAPPS2015
New Contributor
Hello Himanshu,,

Execellent work on IEX.

I am trying to run one of the function from iex_q.q on my windows 10 laptop. getting below error.

it seems some setup to be done before we run this. please help me out.

q)get_last_trade[`IBM]
'conn. OS reports: The requested protocol has not been configured into the system, or no implementation for it exists.
  [2]  C:\q\w32\iex_q.q:19: get_data:{[main_url;suffix;prefix;char_delta;identifier]
  result: (`$":https://",main_url) suffix," ",prefix;
          ^
  (char_delta + first result ss identifier) _ result
q))\

Hi Anand

 

Looks like you need to set up OpenSSL. You can download the installer for Windows from here: https://slproweb.com/products/Win32OpenSSL.html (you probably want to download “Win32 OpenSSL v1.1.0g Light”)

 

You’ll also need a certificate file – you can download this from here: https://curl.haxx.se/ca/cacert.pem

 

Finally in your command prompt, before running q, you need to run this command to set an environment variable:

 

set SSL_CA_CERT_FILE=C:\path\to\cacert.pem

 

(replacing path with correct path to wherever you downloaded cacert.pem)

 

Note the above “set” command will only be valid for the current command prompt session. To set it permanently, use the following instead:

 

setx SSL_CA_CERT_FILE C:\path\to\cacert.pem

 

This won’t set the variable in the current session, but will set it for all future sessions.

 

After doing these setup steps, HTTPS downloads should function correctly on Windows

 

Regards

Jonathon

 

From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of Anand C
Sent: 03 January 2018 10:18
To: Kdb+ Personal Developers <personal-kdbplus@googlegroups.com>
Subject: [personal kdb+] Re: q/kdb+ api for getting market and financial data from IEX

 

Hello Himanshu,,

 

Execellent work on IEX.

 

I am trying to run one of the function from iex_q.q on my windows 10 laptop. getting below error.

 

it seems some setup to be done before we run this. please help me out.

 

q)get_last_trade[`IBM]
'conn. OS reports: The requested protocol has not been configured into the system, or no implementation for it exists.
  [2]  C:\q\w32\iex_q.q:19: get_data:{[main_url;suffix;prefix;char_delta;identifier]
  result: (`$":https://",main_url) suffix," ",prefix;
          ^
  (char_delta + first result ss identifier) _ result
q))\



On Monday, November 13, 2017 at 12:00:15 PM UTC, Himanshu Gupta wrote:

Figured I would post this here as many of you might find this useful.

 

This weekend, I wrote a library to get IEX data in kdb. You can get all sorts of data including EOD summary (high, low, open, close, vwap), earnings (actual vs consensus EPS), financials and news. 

 

 

P.S. Through this project, I learnt that q has an operator for parsing json messages which I wasn't expecting. I just wish it had a more descriptive name than -29!. 🙂

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.


This e-mail message is intended to be received only by persons entitled to receive the confidential information it may contain. E-mail messages to clients of AquaQ Analytics may contain information that is confidential and legally privileged. Please do not read, copy, forward, or store this message unless you are an intended recipient of it. If you have received this message in error, please forward it to the sender and delete it completely from your computer system.

jjttjj
New Contributor
I've tried following these instructions as well as the kx SSL cookbook several times but cannot get this working for the life of me. I can't get past the following error:

OS reports: The requested protocol has not been configured into the system, or no implementation for it exists.

I have tried many versions of openssl and verified they work from the command line before running q, made sure the required windows DLLs are correctly on the PATH.  I also have tried setting SSL_VERIFY_SERVER to "NO". Nothing works, I keep getting this same error.

I'm on Windows 10. 

Does anyone know how I might be able to proceed to debug things from here to get this working?

charlie
New Contributor II
New Contributor II
justin, are you using the 32 or 64bit version of kdb+? Please can you include the startup banner in your email?
You'll need to install 32bit ssl libs if using 32bit kdb+, and 64bit ssl libs if using 64bit kdb+.

hth,
Charlie

jjttjj
New Contributor
I'm using the 32 bit version, banner is as follows:

Welcome to kdb+ 32bit edition
For support please see http://groups.google.com/d/forum/personal-kdbplus
Tutorials can be found at http://code.kx.com/wiki/Tutorials
To exit, type \\
To remove this startup msg, edit q.q

I just tried uninstalling all versions of openssl and then installing it fresh, version "Win32 OpenSSL v1.1.0i" but am still getting the same error. 

charlie
New Contributor II
New Contributor II
kdb+ does not yet work with openssl 1.1, you'll need an older version -

charlie
New Contributor II
New Contributor II
btw, if you prefer the 64bit kdb+, you can apply for a kod personal license from https://ondemand.kx.com

jjttjj
New Contributor
That did it, everything's working now, thanks so much!