Working with Microsoft Excel™¶
Interfacing via HTTP and CSV files¶
Assume that a kdb+ server process is listening on port 5001. Then an HTTP client can send a request that will return a CSV file. For instance, you can type this URL into a browser
http://localhost:5001/q.csv?select from trade where i < 10
to get the first 10 trades. Depending on your browser settings, the result will be opened directly in Excel, saved to a CSV file, etc. The resulting CSV file would look something like this:
stock,price,amount,time goog,75.43086,1800,05:21:48.815 amzn,96.28739,1400,03:46:53.366 goog,4.82224,2700,19:21:25.970 amd,34.25556,2400,16:00:29.397 msft,79.84078,1800,10:46:41.918 ibm,85.37164,1700,08:51:43.909 intel,60.03132,1900,08:17:48.629 amd,48.66041,2200,00:59:15.559 ibm,97.46072,1000,00:50:52.943 ibm,7.951954,1200,20:21:11.319
Alternatively a command-line HTTP client, such as
wget, can also be used:
wget -O output.csv "http://localhost:5001/q.csv?select from trade where i < 10"
This saves the result of the query to the file output.csv, which can be loaded into Excel later.
The result must be a table, so that it can be converted to a CSV file. For instance, the following is invalid:
wget -O output.csv "http://localhost:5001/q.csv?first trade"
because the result is a dictionary. We need
1 # trade. Notice that the
# symbol cannot be written literally in a URL.
wget -O output.csv "http://localhost:5001/q.csv?1 %23 trade"
Interfacing via CSV files¶
CSV files can also be generated by a q process, without using HTTP. For instance, the result of the previous query can be saved into a table and then to a file:
q)output: select from trade where i < 10 q)save `:output.csv
Excel automation add-ins¶
With Automation add-ins for Excel, you can use a C# function in a cell formula. This function can communicate with a kdb+ server process.
Writing Custom Excel Worksheet Functions in C#
Real-time interface via Excel RTD¶
It is possible to have Excel display changing data dynamically using the RTD (real-time data) functionality. Charles Skelton has developed a RTD server for q.
This server is a .NET application, and it communicates directly with a q Ticker Plant, or a chained ticker plant. The RTD feature allows real-time data coming in from the ticker plant to be displayed in Excel. The schema can be customized according to whatever table names and column names are present in the ticker plant.
The RTD server can be downloaded from CharlesSkelton/excelrtd
To install, run the
setup.exe program and follow the instructions. You will need the Microsoft .NET Runtime installed.
Change directory to the location where you installed the program. At that location you will see the file
which is an XML file that contains config information for the server. Change the
port keys in this file to connect to your ticker plant or chained ticker plant. Chained ticker plants are recommended as they provide some level of protection to your primary ticker plant.
name key indicates the logical name of the plant, and is referenced from within the Excel worksheet.
For troubleshooting, a log can be activated – the log directory is specified in the config file under the
logdir key. The program will create log files in the format
logdir/log\_hhmmssfff.txt. Several levels of tracing are available:
The RTD Server can also “fill” data on your behalf – should a null value be received from your ticker plant, the RTD server will use the last non-null value received for that cell instead.
Example RTD file¶
The distribution contains an example Excel file that works with the default schema for demo trade and quote schema shipped with q.
The format for requesting data from the RTD Server is
The RTD server can also store the recent history of a cell, and this can be made available by using an index into the history as an additional parameter to the RTD call, e.g.
will get the previous value of the cell. This can be useful for conditional formatting or perhaps triggering some other calculation. Other cells can be dependent on cells using the RTD function, as can series in charts.
Adjusting the update rate for Excel¶
To set the engine to handle a larger volume of updates, in Excel complete the following:
- In Excel, go to the Visual Basic Editor, by pressing Alt+F11 or clicking Tools > Macro > Visual Basic Editor
- In the Immediate window (press Ctrl+G or click View > Immediate Window), type:
Application.RTD.ThrottleInterval = 1000 ? Application.RTD.ThrottleInterval