An intellij keyword file is now available to provide syntax highlighting of kdb code in intellij:
q Code Intellij Highlighting
To install it copy this xml file to this directory:
C:\Users\USERNAME\.IdeaIC14\config\filetypes
Where USERNAME is obviously your username. Then restart intellij and open a .q file.
We’ve updated our notepad++ qlang.xml to provide code folding and highlighting of the .Q/.z namespaces.
We’ve now posted all source code from this website on our github kdb page.
Additionally we are open sourcing qunit, our kdb testing framework.
We look forward to receiving pull requests to fix our (hopefully few) bugs.
Since our last qStudio kdb+ IDE announcment we have added a lot of new features:
Bulk importing kdb server lists
There’s a lot of new features to allow supporting a huge number of servers efficiently:
In a world overran with open source big data solutions is kdb+ going to be left behind? I hope not…
Every few weeks someone comes to me with a big data problem often with a half-done mongoDB/NoSQL solution that they “want a little help with”. Usually after looking at it I think to myself
“I could solve this in 20 minutes with 5 lines of q code”
But how do I tell someone they should use a system which may end up costing them £1,000s per core in licensing costs. Yes it’s great that there’s a free 32-bit trial version but the risk that you may end up needing the 64-bit is too great a risk.
Given the ever-increasing number of NoSQL solutions and in particular the rising popularity of Hadoop, R, python and MongoDB it’s not hard to see that open-source is taking over the world. Right now kdb+ still has the edge, that it’s faster, sleeker, sexier..but I don’t think that will save it in the long run. The power of open-source is that it let’s everyone contribute, witness the 100’s of libraries available for R, the 1000’s of javascript frameworks. The truly sad thing is that it’s not always the best underlying technology that wins. A 1000 amateurs creating a vibrant ecosystem of plug-ins, add-ons, tutorials… can beat other technologies through sheer force of numbers.
I believe kdb+ is the best solution to a large number of “big data” problems (small data to kdb+), When you stop and think, time-series data is everywhere, open sourcing kdb+ would open up entirely new sectors to kdb+ and I hope it’s a step kx take before it’s too late.
What do you think? Leave your comments below.
Based on user requests we have released a number of new features with qStudio 1.36:
Download the latest ->qStudio<- now.
Which can be set under settings->preferences
To expand a panel into a new window click the “pop-out” icon.
This will bring up the result in a new window:
kdb+ London Contract – £800 p/day
kdb+ belfast Citigroup Contract £400 p/day
Java Poland £150 p/day
sqlDashboards are included as a bundle with qStudio, part of that package is a command line utility called sqlChart that allows generating customized sql charts from the command line.
Checkout the video to see how you can create a chart based on data from a kdb+ database in 2 minutes:
The sqlChart page has all the documentation you need, Download the qstudio.zip to try it now.
Kdb does not have built-in functions for bitwise and,or,xor operations, we are going to create a C DLL extension that provides bitwise operators.
To download the source and definitions for and/or/xor bitwise operations click here
If your new to extending Kdb+ see this tutorial on writing a DLL for Kdb+
I also thought this would be a good opportunity to look at using q-code to generate C code, to generate Kdb Dll’s. When converting an existing library to work with Kdb or writing similar functions for different types, there’s a lot of repetitive coding. Either you can use macros or you can use one language to generate another, to save a lot of typing. Let’s look at bitwise operations as an example of what I mean:
If I wanted to write a bitwise and function band that takes two lists and performs a corresponding operation, we could write the C functions like so:
Notice the similarity between the bandJJ and bandII functions. Plus if we want to handle the case where the second argument is an atom we need an entire other set of C functions similar to bandJ to handle that. This is soon beginning to spiral beyond an easy copy paste job. Instead I used the following 20 lines of q code to generate the 130 lines of C code:
This generates my C code, a .def file exporting the functions that I want to provide and creates a .q file that loads those functions into kdb. Which means any time I add a function or want to support a new type, everything is done for me. let’s look at our functions in action:
Yes the q code is messy (it could be improved) but the concept of dynamically generating your imports can be a real time saver.
This code is not intended for reliability or performance, use at your own risk! If however bitwise operations are a topic that interest you I recommend this article on: Writing a fast vectorised OR function for Kdb.
Often at the start of one of our training courses I’m asked why banks use the Kdb Database for their tick data.
To demonstrate this I’d like to look at three example queries, comparing qSQL to standard SQL.
From the table below we would like to find the price change between consecutive rows.
|
In kdb qSQL this would be the extremely simple and readable code:
In standard SQL there are a few methods, we can use. The simplest is if we already have a sequential id column present:
Even for this simple query our code is much longer and not as clear to read. If we hadn’t had the id column we would have needed much more code to create a temporary table with row numbers. As our queries get more complex the situation gets worse.
Given a table of stock trade prices at various times today, find the top two trade prices for each ticker.
|
In q code this would be select 2 sublist desc price by sym from trade, anyone that has used kdb for a few days could write the query and it almost reads like english. Select the prices in descending order by sym and from those lists take the first 2 items (sublist).
In standard SQL a query that depends on order is much more difficult , witness the numerous online posts with people having problems: stackoverflow top 10 by category, mysql first n rows by group, MS-SQL top N by group. The nicest solution, if your database supports it, is:
The SQL version is much harder to read and will require someone with more experience to be able to write it.
Lastly we want to consider performing a time based join. A common finance query is to find the prevailing quote for a given set of trades. i.e. Given the following trade table t and quote table q shown below, we want to find the prevailing quote before or at the exact time of each trade.
|
|
In qSQL this is: aj[`sym`time; t; q], which means perform an asof-join on t, looking up the nearest match from table q based on the sym and time column.
In standard SQL, again you’ll have difficulty: sql nearest date, sql closest date even just the closest lesser date isn’t elegant. One solution would be:
It’s worth pointing out this is one of the queries that is typically extremely slow (minutes) on row-oriented databases compared to column-oriented databases (at most a few seconds).
Looking at the simplicity of the qSQL code compared to the standard SQL code we can see how basing our database on ordered lists rather than set theory is much more suited to time-series data analysis. By being built from the ground up for ordered data and by providing special time-series based joins, kdb let’s us form these example queries using very simple expressions. Once we need to create more complex queries and nested selects, attempting to use standard SQL can quickly spiral into a verbose unmaintainable mess.
I won’t say qSQL can’t be cryptic 🙂 but for time-series queries qSQL will mostly be shorter and simpler than trying to use standard SQL.
If you think you have shorter SQL code that solves one of these examples or you are interested in one of our kdb training courses please get in touch.
In this tutorial we are going to recreate this example RSI calculation in q, the language of the kdb database.
The relative strength index (RSI) is a technical indicator used in the analysis of financial markets. It is intended to chart the current and historical strength or weakness of a stock or market based on the closing prices of a recent trading period. The RSI is classified as a momentum oscillator, measuring the velocity and magnitude of directional price movements. Momentum is the rate of the rise or fall in price.
The RSI computes momentum as the ratio of higher closes to lower closes: stocks which have had more or stronger positive changes have a higher RSI than stocks which have had more or stronger negative changes. The RSI is most typically used on a 14 day timeframe, measured on a scale from 0 to 100, with high and low levels marked at 70 and 30, respectively. Shorter or longer timeframes are used for alternately shorter or longer outlooks. More extreme high and low levels—80 and 20, or 90 and 10—occur less frequently but indicate stronger momentum.
.
We are going to use the following example data, you can download the csv here or the excel version here.
| Date | QQQQ Close | Change | Gain | Loss | Avg Gain | Avg Loss | RS | 14-day RSI |
|---|---|---|---|---|---|---|---|---|
| 2009-12-14 | 44.34 | |||||||
| 2009-12-15 | 44.09 | -0.25 | 0.00 | 0.25 | ||||
| 2009-12-16 | 44.15 | 0.06 | 0.06 | 0.00 | ||||
| 2009-12-17 | 43.61 | -0.54 | 0.00 | 0.54 | ||||
| 2009-12-18 | 44.33 | 0.72 | 0.72 | 0.00 | ||||
| 2009-12-21 | 44.83 | 0.50 | 0.50 | 0.00 | ||||
| 2009-12-22 | 45.10 | 0.27 | 0.27 | 0.00 | ||||
| 2009-12-23 | 45.42 | 0.33 | 0.33 | 0.00 | ||||
| 2009-12-24 | 45.84 | 0.42 | 0.42 | 0.00 | ||||
| 2009-12-28 | 46.08 | 0.24 | 0.24 | 0.00 | ||||
| 2009-12-29 | 45.89 | -0.19 | 0.00 | 0.19 | ||||
| 2009-12-30 | 46.03 | 0.14 | 0.14 | 0.00 | ||||
| 2009-12-31 | 45.61 | -0.42 | 0.00 | 0.42 | ||||
| 2010-01-04 | 46.28 | 0.67 | 0.67 | 0.00 | RS | RSI | ||
| 2010-01-05 | 46.28 | 0.00 | 0.00 | 0.00 | 0.24 | 0.10 | 2.39 | 70.53 |
| 2010-01-06 | 46.00 | -0.28 | 0.00 | 0.28 | 0.22 | 0.11 | 1.97 | 66.32 |
| 2010-01-07 | 46.03 | 0.03 | 0.03 | 0.00 | 0.21 | 0.10 | 1.99 | 66.55 |
| 2010-01-08 | 46.41 | 0.38 | 0.38 | 0.00 | 0.22 | 0.10 | 2.27 | 69.41 |
| 2010-01-11 | 46.22 | -0.19 | 0.00 | 0.19 | 0.20 | 0.10 | 1.97 | 66.36 |
| 2010-01-12 | 45.64 | -0.58 | 0.00 | 0.58 | 0.19 | 0.14 | 1.38 | 57.97 |
| 2010-01-13 | 46.21 | 0.57 | 0.57 | 0.00 | 0.22 | 0.13 | 1.70 | 62.93 |
| 2010-01-14 | 46.25 | 0.04 | 0.04 | 0.00 | 0.20 | 0.12 | 1.72 | 63.26 |
| 2010-01-15 | 45.71 | -0.54 | 0.00 | 0.54 | 0.19 | 0.15 | 1.28 | 56.06 |
| 2010-01-19 | 46.45 | 0.74 | 0.74 | 0.00 | 0.23 | 0.14 | 1.66 | 62.38 |
| 2010-01-20 | 45.78 | -0.67 | 0.00 | 0.67 | 0.21 | 0.18 | 1.21 | 54.71 |
| 2010-01-21 | 45.35 | -0.43 | 0.00 | 0.43 | 0.20 | 0.19 | 1.02 | 50.42 |
| 2010-01-22 | 44.03 | -1.33 | 0.00 | 1.33 | 0.18 | 0.27 | 0.67 | 39.99 |
| 2010-01-25 | 44.18 | 0.15 | 0.15 | 0.00 | 0.18 | 0.26 | 0.71 | 41.46 |
| 2010-01-26 | 44.22 | 0.04 | 0.04 | 0.00 | 0.17 | 0.24 | 0.72 | 41.87 |
| 2010-01-27 | 44.57 | 0.35 | 0.35 | 0.00 | 0.18 | 0.22 | 0.83 | 45.46 |
| 2010-01-28 | 43.42 | -1.15 | 0.00 | 1.15 | 0.17 | 0.29 | 0.59 | 37.30 |
| 2010-01-29 | 42.66 | -0.76 | 0.00 | 0.76 | 0.16 | 0.32 | 0.49 | 33.08 |
| 2010-02-01 | 43.13 | 0.47 | 0.47 | 0.00 | 0.18 | 0.30 | 0.61 | 37.77 |
The formulas behind the calculations used in the table are:
All subsequent gains than the first use the following:
We can load our data (rsi.csv) in then apply updates at each step to recreate the table above:
Code kindly donated by Terry Lynch
Rather than create all the intermediate columns we can create a calcRsi function like so:
Finally we can visualize our data using the charting functionality of qStudio (an IDE for kdb):
RSI Relative Strength Index stock chart for QQQQ created using qStudio
Or to plot RSI by itself (similar to original article
RSI Line Graph in qStudio
Writing kdb analytics such as Relative Strength Index is covered in our kdb training course, we offer both public kdb training courses in New York, London, Asia and on-site kdb courses at your offices, tailored to your needs.