Interact with excel from KDB

Community Support
1

Hi

I’m totally new to KDB( only learned about the syntax) and have a problem when trying to start my first practice. 

I have a stock price series stored in KDB and it is updated in sync with the stock exchange. Now I want to pass the price series from KDB to VBA (not the who series, but the latest certain number of data points) and conduct some further calculations. As the price series keeps being updated, the final result displayed in a cell should be dynamic.

I’ve tried Charlie’s K4RTDServer, with which I could only get the latest price rather than a part of the price series.  However, without K4RTDServer I don’t know how to get a dynamic result in a excel cell.  

Ideally, I want to write a excel function by vba and get a computed result in the return.For example a vba function: 

result=kdbCalculator(kdbserver,dataset,number of data points)

Is it possible to do so? Thank you so much!  

2

Hi

Devnet built an Excel interface which they open sourced:

https://github.com/exxeleron/qXL

It supports request/response which I think is what you are after.

Thanks 

Jonny

3

Thank you so much Jonny! It seems to be what I need. I will take a look at it and hope it works for me.

4

Hmmm,<o:p></o:p>

<o:p> </o:p>

>>> (not the who series, but the latest certain number of data points) and conduct some further calculations. As the price series keeps being updated, the final result displayed in a cell should be dynamic<o:p></o:p>

<o:p> </o:p>

I think he is more looking for a ctp/cep. So only installing/using qxl might not be enough.<o:p></o:p>

<o:p> </o:p>

Perhaps you can describe more what you want to achieve. What is your project. What is your current setting ( Tp, Rdb, hdb,…). Do you know what is tickplus so we can give you more appropriate advices.<o:p></o:p>

<o:p> </o:p>

Kim<o:p></o:p>

<o:p> </o:p>

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Jonny Press
Gesendet: Mittwoch, 24. Februar 2016 14:31
An: personal-kdbplus@googlegroups.com
Betreff: Re: [personal kdb+] Interact with excel from KDB<o:p></o:p>

<o:p> </o:p>

Hi<o:p></o:p>

<o:p> </o:p>

Devnet built an Excel interface which they open sourced:<o:p></o:p>

<o:p> </o:p>

https://github.com/exxeleron/qXL<o:p></o:p>

<o:p> </o:p>

It supports request/response which I think is what you are after.<o:p></o:p>

<o:p> </o:p>

Thanks <o:p></o:p>

<o:p> </o:p>

Jonny<o:p></o:p>

<o:p> </o:p>

On 24 Feb 2016, at 10:25, Xinyu Gai <xinyu.gai@ucdconnect.ie> wrote:<o:p></o:p>

<o:p> </o:p>

Hi<o:p></o:p>

<o:p> </o:p>

I’m totally new to KDB( only learned about the syntax) and have a problem when trying to start my first practice. <o:p></o:p>

<o:p> </o:p>

I have a stock price series stored in KDB and it is updated in sync with the stock exchange. Now I want to pass the price series from KDB to VBA (not the who series, but the latest certain number of data points) and conduct some further calculations. As the price series keeps being updated, the final result displayed in a cell should be dynamic.<o:p></o:p>

<o:p> </o:p>

I’ve tried Charlie’s K4RTDServer, with which I could only get the latest price rather than a part of the price series.  However, without K4RTDServer I don’t know how to get a dynamic result in a excel cell.  <o:p></o:p>

<o:p> </o:p>

Ideally, I want to write a excel function by vba and get a computed result in the return.For example a vba function: <o:p></o:p>

result=kdbCalculator(kdbserver,dataset,number of data points)<o:p></o:p>

<o:p> </o:p>

Is it possible to do so? Thank you so much!  <o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

–
Submitted via Google Groups

5

Hi Kim, 

Thanks a lot for your kind reply.

As I’m a newbie to KDB, I couldn’t understand some of the abbreviations in your post. I searched them on google, does “ctp/cep” short for “convertible trading platforms/complex event processing”?  

By saying “current setting”, do you mean the tick+ architecture?  I’m not quite sure about this, but I think I have access to a chained tickerplant as well as hdb and rdb. (I don’t know whether this expression is correct, what I want to say is that I can get real-time data and historical data from the database, but I don’t know whether the real-time data comes from a chained tickerplant or a rdb). 

==========What I want to achieve =========

In general, my goal is to build up some treasury futures spreads and display its real-time price in EXCEL. 

To be more specific, say a futures spread: 3 contracts of 10-year treasury note futures - 2 contracts of 5-year treasury note futures - 1 contract of 2-year treasury note futures   ( 3*ZN - 2*ZF - ZT ) ;

I need to construct the spread with a ratio of  (3,-2,-1), then figure out how to display the real-time price in excel. I think this could be very complicated as I need it to be dynamic.  

Further more, I want to do some calculations with the price. Probably I need to get Open/High/Low/Close price (OHLC) of the spread from hdb. So far I haven’t figured out how to synchronize the tick data to get accurate real-time spread price. Once tick data is correctly synchronised, I can build up spreads between different futures contracts.   Ideally I want the calculation results to be dynamically displayed in EXCEL as well. 

Another problem is, I need to aggregate tick data to certain time frame to get corresponding OHLC.  Just like a candlestick chart, if I’m looking at it with a  5-minute time frame, tick data needs to be aggregated in order to get OHLC of each 5-minute window.

====================================

Could you please give me some suggestions?  

Probably it is a very complicated problem, any suggestions are sincerely appreciated! 

Thanks, 

Xinyu