1. Introduction
This article provides an introduction to the deployment of KDB+ and essential interaction with the database engine using Golang in a scope of a trading application. There is excellent documentation provided from the KX side related to their core product KDB+. However, there are few articles or tutorials for Golang and KDB+ yet. I hope this article will help you as a starting point.
Who does not know KDB+ there is a brief introduction A General Introduction to KDB+/Q for Wall Street Data Scientist and Quantitative Analyst [1]
There is a myth around the KDB+ that puts the database into a category of “trading proprietary” and “very expensive”. I am unaware of why that happened and what was the original case behind the myth. I believe there will be comments related to that. The only explanation could be the long learning curve to master the proprietary programming/scripting language. Also, the advanced KDB+ functions related to trading analytics and ML need some background and experience. That probably triggered the job posts on LinkedIn with exposed salary are reaching £300,000+ (2022) and has been staying on this level consistently for a few years.
The learning journey take usually several month up to a year for someone with a solid software engineering background, experience, and a little patience and curiosity for proprietary languages like Q. The best start is the 230 pages long whitepaper[2] from Emanuele Melis called “Compendium of Technical Whitepapers”, which is more of a consolidated extract from all the KX whitepapers.
2. KDB+ and Golang use-case
First, we need setup an environment:
d) Download the KDB+ database from the official (personal, non-commercial version) executable binary from https://code.kx.com/q/learn/install/ and pick the 64-bit version. KX will send you an email with a link for download and the license key file in the attachment. Put the unzipped folder under ~/q folder and set $PATH for q inside ~/q/l64/q or ~/q/m64/q for MacOS. Add a licence to the same directory and type q in the command line. Is that all? Is KDB+ the 800kB q file? The answer is yes.
b) Run KDB+ as a daemon on default port 5000 requires running this command: (for MacOS q/m64/q)
/ The command will run KDB+ as a service
nohup q/l64/q -p 5000 < /dev/null > /tmp/stdoe 2>&1&
/ Example of insert command
/ tab:flip `items`sales`prices!(`nut`bolt`cam`cog;6 8 0 3;10 20 15 20)More detail on architecture can be found in the Aquaq Analytics course presentation from Jonny Press and Jamie Grant [3]. Mainly the diagrams can be useful for better understanding some of the integration patterns later.
c) To test if everything is up and running:
/ remote connection to kdb+ on port 5000
h:hopen `::5000
/ list of tables (should return `symbol)
h "tables[]"d) All is ready to load data using Golang. Let’s open a demo trading account with Oanda.com to get a real-time stream of FX data.
Open demo account: https://www.oanda.com/apply/demo/
The account should be available immediately. What you will need next is an API access token. You can get it under the My Services / Manage API Access link. Save the token somewhere safe; you won’t be able to see it anymore.
The second parameter to set up a connection is the account number you can find when you launch a web client. (Note: It’s not the best client; however, it’s very stable with a clean design and TradingView.com features)
Create a new folder for the Golang project and add a file called “.env”, and add your API key and account number:
OANDA_API_KEY=dff88305c9b9f6648317a9748e20c01c-c1b9007f147672328a0dbe5974cb539
OANDA_ACCOUNT_ID=101–004–14245069–001e) Finally, a little bit of Golang. Our application will use Goanda lib https://github.com/AwolDes/goanda. There are a few lines which I have to explain:
ts := &kdb.K{kdb.KP, kdb.NONE, []time.Time{local}}- KP (timestamp), KS (symbol/string), KF (float), TX (table) are data types of KDB+. (link https://code.kx.com/q/interfaces/capiref/).
- KDB Go library, which seems to cover most of the functions, is https://github.com/sv/kdbgo.
- KP data type accepts time. Time type is automatically converted to proprietary format, e.g., 2021.04.30D09:30:01.000000000
constant associated type value
- - - - - - - - - - - - - - - -
KB boolean 1
UU guid 2
KG byte 4
KH short 5
KI int 6
KJ long 7
KE real 8
KF float 9
KC char 10
KS symbol 11
KP timestamp 12
KM month 13
KD date 14
KZ datetime 15
KN timespan 16
KU minute 17
KV second 18
KT time 19
XT table 98
XD dictionary 99Code is using ticker 5 seconds + 23 hours time limit (trading day 23:00–22:00):
package mainimport (
"log"
"os"
"strconv"
"time" "github.com/joho/godotenv"
"github.com/awoldes/goanda" kdb "github.com/sv/kdbgo" lr "github.com/sirupsen/logrus"
prefixed "github.com/x-cray/logrus-prefixed-formatter"
)var logr = &lr.Logger{
Out: os.Stdout,
Level: lr.InfoLevel,
Formatter: &prefixed.TextFormatter{
DisableColors: false,
TimestampFormat: "2006-01-02 15:04:05",
FullTimestamp: true,
ForceFormatting: true,
},
}func main() {
err := godotenv.Load()
if err != nil {
log.Fatal("Error loading .env file")
} key := os.Getenv("OANDA_API_KEY")
accountID := os.Getenv("OANDA_ACCOUNT_ID") oanda := goanda.NewConnection(accountID, key, false)
// connection string for kdb+
con, err := kdb.DialKDB("localhost", 5000, "")
// instruments to load to kdb+
instruments := []string{"XAU_USD", "GBP_USD", "EUR_GBP"}
logr.Info("Oanda.com pricing producer is running...") ticker := time.NewTicker(5 * time.Second)
done := make(chan bool)
go func() {
for {
select {
case <-done:
return
case _ = <-ticker.C:
orderResponse := oanda.GetPricingForInstruments(instruments)
for _, resp := range orderResponse.Prices {
askprice, err := strconv.ParseFloat(resp.CloseoutAsk, 64)
bidprice, err := strconv.ParseFloat(resp.CloseoutBid, 64)
midprice := (askprice + bidprice) / 2
local := resp.Time
if err != nil {
logr.Errorf("Failed to connect: %v", err)
return
} ts := &kdb.K{kdb.KP, kdb.NONE, []time.Time{local}}
source := &kdb.K{kdb.KS, kdb.NONE, []string{"oanda"}}
sym := &kdb.K{kdb.KS, kdb.NONE, []string{resp.Instrument}}
ask := &kdb.K{kdb.KF, kdb.NONE, []float64{askprice}}
bid := &kdb.K{kdb.KF, kdb.NONE, []float64{bidprice}}
mid := &kdb.K{kdb.KF, kdb.NONE, []float64{midprice}}
st := &kdb.K{kdb.KS, kdb.NONE, []string{resp.Status}} logr.Info(ts, source, sym, ask, bid) tab := &kdb.K{kdb.XT, kdb.NONE, kdb.Table{[]string{"ts", "source", "sym", "ask", "mid", "bid", "status"}, []*kdb.K{ts, source, sym, ask, mid, bid, st}}} // insert tab sync
_, err = con.Call("insert", &kdb.K{-kdb.KS, kdb.NONE, "prices"}, tab)
if err != nil {
logr.Errorf("Insert Query failed: %v", err)
return
}
}
}
}
}() for {
time.Sleep(23 * time.Hour)
err := con.Close()
if err != nil {
return
}
}
}Before you run the code, make sure you have all the dependencies. Go mod can help to pull (go get) all that required.
go mod init <name of the project>
go mod tidyOutput (timestamp, source, instrument, ask, bid):
janrock@uni01:~/scratch$ go run main.go
[2021–05–01 11:46:50] INFO Oanda.com pricing consumer is running…
2021/05/01 11:46:56 [2021–04–30 20:59:56.37640811 +0000 UTC] [oanda] [XAU_USD] [1774.445] [1769.445]
2021/05/01 11:46:56 [2021–04–30 20:59:54.187563098 +0000 UTC] [oanda] [GBP_USD] [1.38253] [1.38099]
2021/05/01 11:46:56 [2021–04–30 20:59:53.867267314 +0000 UTC] [oanda] [EUR_GBP] [0.87061] [0.86907]2021/05/01 11:47:00 [2021–04–30 20:59:56.37640811 +0000 UTC] [oanda] [XAU_USD] [1774.445] [1769.445]
2021/05/01 11:47:00 [2021–04–30 20:59:54.187563098 +0000 UTC] [oanda] [GBP_USD] [1.38253] [1.38099]
2021/05/01 11:47:00 [2021–04–30 20:59:53.867267314 +0000 UTC] [oanda] [EUR_GBP] [0.87061] [0.86907]
Content of the table can be printed from q REPL: (note: -10 means the same as limit 10 after sorting in SQL)Don’t forget to set up a remote connection!!!
q)h "select [-10] from `prices"
ts source sym ask mid bid status
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2021.04.30D20:59:53.867267314 oanda EUR_GBP 0.87061 0.86984 0.86907 non-tradeable
2021.04.30D20:59:56.376408110 oanda XAU_USD 1774.445 1771.945 1769.445 non-tradeable
2021.04.30D20:59:54.187563098 oanda GBP_USD 1.38253 1.38176 1.38099 non-tradeable
2021.04.30D20:59:53.867267314 oanda EUR_GBP 0.87061 0.86984 0.86907 non-tradeable
2021.04.30D20:59:56.376408110 oanda XAU_USD 1774.445 1771.945 1769.445 non-tradeable
2021.04.30D20:59:54.187563098 oanda GBP_USD 1.38253 1.38176 1.38099 non-tradeable
2021.04.30D20:59:53.867267314 oanda EUR_GBP 0.87061 0.86984 0.86907 non-tradeable
2021.04.30D20:59:56.376408110 oanda XAU_USD 1774.445 1771.945 1769.445 non-tradeable
2021.04.30D20:59:54.187563098 oanda GBP_USD 1.38253 1.38176 1.38099 non-tradeable
2021.04.30D20:59:53.867267314 oanda EUR_GBP 0.87061 0.86984 0.86907 non-tradeablef) Data are stored as an in-memory table object. There is an option to store the table snapshots on disk etc. (maybe I will add it later to this article). Now we are going to load the data from KDB+ to Golang.
Very similar code, just with a struct binding output from “sql” type of select.
package mainimport (
"os"
"time"
kdb "github.com/sv/kdbgo"
lr "github.com/sirupsen/logrus"
prefixed "github.com/x-cray/logrus-prefixed-formatter"
)var logr = &lr.Logger{
Out: os.Stdout,
Level: lr.InfoLevel,
Formatter: &prefixed.TextFormatter{
DisableColors: false,
TimestampFormat: "2006-01-02 15:04:05",
FullTimestamp: true,
ForceFormatting: true,
},
}type TIAB struct {
Ts time.Time
Instrument string
Ask float64
Bid float64
}func toStruct(tbl kdb.Table) []TIAB {
var data = []TIAB{}
nrows := int(tbl.Data[0].Len())
for i := 0; i < nrows; i++ {
rec := TIAB{Ts: tbl.Data[0].Index(i).(time.Time), Instrument: tbl.Data[1].Index(i).(string), Ask: tbl.Data[2].Index(i).(float64), Bid: tbl.Data[3].Index(i).(float64)}
data = append(data, rec)
}
return data
}func main() {
con, err := kdb.DialKDB("localhost", 5000, "")
if err != nil {
logr.Errorf("Failed to connect: %v", err)
} logr.Info("Oanda.com pricing consumer is running...")ticker := time.NewTicker(5 * time.Second)
done := make(chan bool)
go func() {
for {
select {
case <-done:
return
case _ = <-ticker.C:
ktbl, err := con.Call("select [-1] ts, sym, ask, bid from prices")
if err != nil {
logr.Errorf("Query failed: %v", err)
return
}
series := toStruct(ktbl.Data.(kdb.Table))
for _, v := range series {
logr.Infof("Timestamp: %v | Intrument: %v | Ask: %v | Big: %v", v.Ts, v.Instrument, v.Ask, v.Bid)
}
}
}
}()
for {
time.Sleep(23 * time.Hour)
err := con.Close()
if err != nil {
return
}
}
}Script output: (note: -1 means the same as limit 1 after sorting in SQL)
janrock@uni01:~/scratch$ go run read.go
[2021–05–01 16:53:23] INFO Oanda.com pricing consumer is running…
[2021–05–01 16:53:28] INFO Timestamp: 2021–04–30 20:59:53.867267314 +0000 UTC | Instrument: EUR_GBP | Ask: 0.87061 | Big: 0.86907g) That’s all! This article covers the most basic operations with KDB+ for Golang. The final application can be far more complex. It can serve data for KX Dashboards, trading strategies, create limit/market orders back to Oanda.com, etc.
h) PS: Commercial licence pricing for GCP:VM instance: 2 vCPUs + 8 GB memory (n2-standard-2) £51.36/mo Solid State Disk: 20GB £0.94/mo Sustained use discount £15.41/mo Estimated monthly total £1,358.92/mo (similar performance you can get from MongoDB — Atlas for ~£1,000/mo)
3. Summary
No algorithm or trader is right every time, but both should make money more often than lose money, and profitable algorithms or trades should make more than the losing ones. The easiest way to do all these things is to use your brain, discipline, stay modest and not expect amazing results from the beginning. Algorithmic trading is not just a financial discipline but also an excellent opportunity to master programming skills within a real-time environment, combining technology, business and economics knowledge. Good luck!
Enjoy it, and if you like the article, please, click on the applause icon below. Please, add comments and ideas for the next KX-related content.
4. Bibliography and Web Sources
[1] A General Introduction to KDB+/Q for Wall Street Data Scientist and Quantitative Analyst. 2021. Medium.com. Online: https://medium.com/codex/a-general-introduction-to-kdb-q-for-data-scientist-quantitative-analyst-4b18ec5336b2 [Accessed: 20/11/2022]
[2] Emanuele Melis. 2021. KX. Online: https://kx.com/wp-content/uploads/2020/09/Kx-Compendium-of-Technical-WhitePapers.pdf [Accessed: 20/11/2022]
[3] Jonny Press and Jamie Grant. 2017. Aquaq Analytics. Online: https://www.aquaq.co.uk/wp-content/uploads/2017/02/kdbArchitectureWorkshop.pdf [Accessed: 20/11/2022]
5. Recommended Books
Borror, J. (2015) q For Mortals Version 3: An Introduction to q Programming. ISBN: 978–0692573679
Psaris, N. (2020) Fun Q: A Functional Introduction to Machine Learning in Q. ISBN: 978–1734467505
Psaris, N. (2015) Q Tips: Fast, Scalable and Maintainable Kdb+. ISBN: 978–9881389909
Bilicon, P. (2019) Machine Learning and Big Data with kdb+/q (Wiley Finance). ISBN: 978–1119404750
