Excel client for q
Interfacing via HTTP and CSV files¶
Assume that a q 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
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
wget, can also be used:
wget -O output.csv "http://localhost:5001/q.csv?select from trade where i < 10"
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"
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 q server process.
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 customised 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
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
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