QforMortals3/Example Trades Table
1.17 Example: Trades Table
In this section we construct a toy trades table to demonstrate the power of q-sql.
￼A useful operator for constructing lists of test data is (?), which generates pseudo-random data. We can generate 10 numbers randomly selected, with replacement, from the first 20 integers starting at 0 (i.e., not including 20).
q)10?20 / ymmv 4 13 9 2 7 0 17 14 9 18 q)10?20 _ q)10?20 _
We can similarly generate 10 random floats between 0.0 and 100.0 (not including 100.0).
￼q) 10?100.0 _
We can make 10 random selections from the items in a list
Now to our trades table. Since a table is a collection of columns, we first build the columns. We apologize for using excessively short names so that things fit easily on the printed page.
First we construct a list of 1,000,000 random dates in the month of January 2015.
Next a list of 1,000,000 timespans.
Next a list of 1,000,000 tickers chosen from AAPL, GOOG and IBM. It is customary to make these lower case symbols.
Next a list of 1,000,000 volumes given as positive lots of 10.
As an initial cut, we construct a list of 1,000,000 prices in cents uniformly distributed within 10% of 100.0. We will adjust this later.
Now collect these into a table and inspect the first 5 records. Remember, a table is a list of records so (#) applies.
q)trades:( dt:dts; tm:tms; sym:syms; vol:vols; px:pxs) q)5#trades _
The first thing you observe in your console display is that the trades are not in temporal order. We fix this by sorting on time within date using (xasc).
q)trades:`dt`tm xasc trades q)5#trades _
Now we adjust the prices. At the time of this writing (Sep 2015) AAPL was trading around 100, so we leave it alone. But we adjust GOOG and IBM to their approximate trading ranges by scaling.
q)trades:update px:6*px from trades where sym=`goog q)trades:update px:2*px from trades where sym=`ibm q)5#trades dt tm sym vol px ------------------------------------------------ 2014.01.01 0D00:00:04.117137193 goog 6140 582.24 2014.01.01 0D00:00:06.227586418 ibm 7030 196.66 2014.01.01 0D00:00:07.611505687 ibm 7740 185.14 2014.01.01 0D00:00:11.415991187 goog 4130 605.34 2014.01.01 0D00:00:12.739158421 goog 8810 579.36
This looks a bit more like real trades. Let’s perform some basic queries as sanity checks. Given that both price and volume are uniformly distributed, we expect their averages to approximate the mean. Using the built-in average function (avg) we see that they do.
q)select avg px, avg vol by sym from trades _
Similarly, we expect the minimum and maximum price for each symbol to be the endpoints of the uniform range.
q)select min px, max px by sym from trades _
Our first non-trivial query computes the 100 millisecond bucketed volume-weighted average price (VWAP). This uses the built-in dyadic function (xbar). The left operand of (xbar) is an interval width and the right operand is a list of numeric values. The effect of (xbar) is to shove each input to the left-hand end point of the interval of specified width in which it falls. For example, ￼
q)5 xbar til 15 0 0 0 0 0 5 5 5 5 5 10 10 10 10 10
This is useful for grouping since it effectively buckets all the values within each interval to the left end-point of that interval. Recalling that a timespan is actually an integral count of nanoseconds since midnight, to compute 100 millisecond buckets we will use (‘’’xbar’’’) with an interval of 100,000,000.
We also require (‘’’wavg’’’), a dyadic function that computes the average of the numeric values in its right operand weighted by the values of its left operand.
q)1 2 3 wavg 50 60 70 _
Now we put things together in a single query. For convenience of display, we group by bucketed time within symbol.
q)select vwap:vol wavg px by sym,bkt:100000000 xbar tm from trades _
That’s all there is to it!
Our final query involves the maximum profit (or analogously, maximum drawdown) realizable over the trading period. To understand the concept, image that you have a DeLorean with flux capacitor and are able to travel into the future and record historical trade results. Upon returning to the present, you are given $1,000,000 to invest with the stipulation that you can make one buy and one sell for AAPL and you are not allowed to short the stock. As a good capitalist your goal is to maximize your profit.
Restating the problem, we wish to determine the optimum time to buy and sell for the largest (positive) difference in price, where the buy precedes the sell. We state the solution as a q koan, which you should contemplate until enlightenment.
q)select max px-mins px from trades where sym=`aapl _
Two hints if Zen enlightenment is slow to dawn.
- Take the perspective of looking back from a potential optimum sell
- The optimum buy must happen at a cumulative local minimum; otherwise, you could back up
Reprinted with the author's permission from: q for Mortals Version 3, An Introduction to Q Programming by Jeffry A. Borror.
©2015 Jeffry A. Borror/ q4m LLC