TimeStored Blog

qStudio Dot Graph Rendering of FIX Order Status

“The Financial Information eXchange (FIX) protocol is an electronic communications protocol initiated in 1992 for international real-time exchange of information related to the securities transactions and markets.”. You can see an example of a FIX message being parsed here.

What we care about is that an order goes through a lifecycle. From newly created to filled or removed. Anything that involves state-transitions or a lifecycle can be visualized as a graph. A graph depicts transitions from one state to another. Often SQL tables record every transition of that state. This can then be summarised into a count of the last state, giving something like the following:

qStudio now automatically converts this result table to DOT format and if you have graphviz“>graphviz installed and on the PATH, will generate the following:

Note I did tweak the table a little to add styling like so:

update style:(`Filled`Removed!("color=green";"color=red")) To,label:(label,'" ",/:cnt) from currentFixStatus

The format is detailed again in our qStudio Chart Data Format page.

This is another even simpler example:

qStudio 1.43 Released – mac save bug fixed

qStudio 1.43 Released. This:

• Adds stack traces to kdb 3.5+
• Fixes the mac bug where the filename wasn’t shown when trying to save a file.
• Fixes a number of multi-threading UI problems

Download it now.

kdb 3.5 Released with Stack Trace

kdb+ 3.5 had a significant number of changes:

• Debugger – At long last we can finally get stack traces when errors occur.
• Concurrent Memory Allocator – Supposedly better performance when returning large results from peach
• Port Reuse – Allow multiple processes to listen on same port. Assuming Linux Support
• Improved Performance – of Sorting and Searching
• Additional ujf function – Similar to uj from v2.x fills from left hand side

kdb Debugger

The feature that most interests us right now is the Debugging functionality. If you are not familiar with how basic errors, exceptions and stack movement is handled in kdb see our first article on kdb debugging here. In this short post we will only look at the new stack trace functionality.

Now when you run a function that causes an error at the terminal you will get the stack trace. Here’s a simple example where the function f fails:

Whatever depth the error occurs at we get the full depth stack trace, showing every function that was called to get there using .Q.bt[]:

The good news is that this same functionality is availabe in qStudio 1.43. Give it a try: qStudio.

Note: the ability to show stack traces relies on qStudio wrapping every query you send to the server with its own code to perform some analysis and return those values. By default wrapping is on as seen in preferences. If you are accessing a kdb server ran by someone else you may have to turn wrapping off as that server may limit which queries are allowed. Unfortunately stack tracing those queries won’t be easily possible.

That’s just the basics, there are other new exposed functions and variables, such as .Q.trp – for trapping calls and accessing traces that we are going to look at in more detail in future.

qStudio 1.41 Release with Custom Security

qStudio 1.41 is now available to download.

It adds the ability to use custom Security Authentications and custom JDBC drivers.
By automatically loading .jar plugins from libs folder.

After a few users reported issues around “watched expressions” we are removing the ctrl+w shortcut as it was often getting used by mistake. The last change was some internal work to improved startup/shutdown logging for debugging purposes..

qStudio kdb IDE 1.40 Released

qStudio 1.40 is now available to download.

The latest changes include:

• No need to save changes before shutdown, unsaved changes stored till reopened.
• Add sqlchart to system path.
• Fix display of tables with underscore in the name.
• Database documenter/report enhancements
• Improved code printing
• FileTreePanel much more efficient at displaying large number of files.

qStudio adds Nested Server Folder Support

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:

• Support importing HUGE number of servers:
  • 5000+ server connections are now supported
  • To prevent massive memory use, the object tree for a server is no longer refreshed at startup only on connection.
  • Allow specifying default username/password once for all servers
  • Allow nested connection folders
  • Add critical color option – servers with prod in name get highlighted in red
• Sort File Tree Alphabetically
• Numerous bugfixes including:
  • Fix critical Mac bug that prevented launching in some instances
  • Fix query cancelling

qStudio kdb+ GUI adds Dark Theme and Chinese Language

Based on user requests we have released a number of new features with qStudio 1.36:

Download the latest ->qStudio<- now.

Dark Code Editor Themes

Which can be set under settings->preferences

Open Results and Charts in New Window

To expand a panel into a new window click the “pop-out” icon.

This will bring up the result in a new window:

UTF-8 Chinese Language Support

Command Line Kdb+ Charts

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.

The q Code

Help Screen

RSI Relative Strength Index in Kdb

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.

.

Stock Price Time-Series Data

We are going to use the following example data, you can download the csv here or the excel version here.

RSI Formulas

The formulas behind the calculations used in the table are:

• First Average Gain = Sum of Gains over the past 14 periods / 14.
• First Average Loss = Sum of Losses over the past 14 periods / 14

All subsequent gains than the first use the following:

• Average Gain = [(previous Average Gain) x 13 + current Gain] / 14.
• Average Loss = [(previous Average Loss) x 13 + current Loss] / 14.

• RS = Average Gain / Average Loss
• RSI = 100 – 100/(1+RS)

Writing the Analytic in q

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.

Exponential Moving Average EMA in Kdb

Let’s look at how to write moving average analytics in q for the kdb database. As example data (mcd.csv) we are going to use stock price data for McDonalds MCD. The below code will download historical stock data for MCD and place it into table t:

Simple Moving Average

The simple moving average can be used to smooth out fluctuating data to identify overall trends and cycles. The simple moving average is the mean of the data points and weights every value in the calculation equally. For example to find the moving average price of a stock for the past ten days, we simply add the daily price for those ten days and divide by ten. This window of size ten days then moves across the dates, using the values within the window to find the average. Here’s the code in kdb for 10/20 day moving average and the resultant chart.

Simple Moving Average Stock Chart Kdb (Produced using qStudio)

What Exponential Moving Average is and how to calculate it

One of the issues with the simple moving average is that it gives every day an equal weighting. For many purposes it makes more sense to give the more recent days a higher weighting, one method of doing this is by using the Exponential Moving Average. This uses an exponentially decreasing weight for dates further in the past.The simplest form of exponential smoothing is given by the formula:

where α is the smoothing factor, and 0 < α < 1. In other words, the smoothed statistic st is a simple weighted average of the previous observation xt-1 and the previous smoothed statistic st−1.

This table displays how the various weights/EMAs are calculated given the values 1,2,3,4,8,10,20 and a smoothing factor of 0.7: (excel spreadsheet)

To perform this calculation in kdb we can do the following:

q)ema:{first[y](1-x)\x*y} q)ema[0.7;1 2 3 4 8 10 20] 1 1.7 2.61 3.583 6.6749 9.00247 16.70074 q)/ the \ here is a very specialised shorthand q)/ The slightly longer version is: q)ema:{{z+x*y}\[first y;1-x;x*y]} q)ema[0.7;1 2 3 4 8 10 20] 1 1.7 2.61 3.583 6.6749 9.00247 16.70074
(This code was originally posted to the google mail list by Attila, the full discussion can be found here)

This backslash adverb works as

The alternate syntax generalizes to functions of 3 or more arguments where the first argument is used as the initial value and the arguments are corresponding elements from the lists:

Exponential Moving Average Chart

Finally we take our formula and apply it to our stock pricing data, allowing us to see the exponential moving average for two different smoothing factors:

Exponential Moving Average Stock Price Chart produced using qStudio

As you can see with EMA we can prioritize more recent values using a chosen smoothing factor to decide the balance between recent and historical data.

Writing kdb analytics such as Exponential Moving Average is covered in our kdb training course, we regularly provide training courses in London, New York, Asia or our online kdb course is available to start right now.