The idea of quant trading is to try and find new predictive signals to deriveprofitable trading strategies. As a result there are a plethora of marketsignals, from simple widespread ones used by retail outlets to complicatedproprietary strategies employed by hedge funds.

In this article, we'll look at using QuestDB queries in aGrafana dashboard to derive and test simpletrading signals. We will use the Coinbase crypto market data as the underlyingsource of data. If you wish to follow along, please refer toour tutorial onsetting up QuestDB and Grafana via Docker or visit theGrafana docs.

This data in particular was pulled using thecryptofeed library. Seethis tutorialto set it up quickly and start ingesting real trades from the largest cryptoexchanges directly into QuestDB.

Calculating a moving average

A moving average computes the average price of a stock over a rolling window oftime. Conveniently, this is a strong use of QuestDB's newly releasedwindow functions.

We can define a moving average in a query as follows:

SELECTtimestamptime,
       symbol,
       price,
avg(price)
OVER(PARTITIONBY symbol
ORDERBYtimestamp
             RANGE1HOURPRECEDING) moving_average_1h,
FROM trades
WHERE $__timeFilter(timestamp)
AND   symbol= $Pairs

The moving average would look like the following…

Moving average as a technical analysis indicator

Technical analysis refers to analysis based solely on chart data. Thiscontrasts with other types of analysis, such asfundamental analysis whichlooks at the underlying value of the assets to determine its market price. Forexample, for a stock price, this would mean analyzing the company's businessoperations and cash flows to derive a value for the stock price.

Moving averages are part of thetechnical analysis spectrum. The typical waymoving averages are looked at from a signal point of view is that the crossingof two moving averages over different time intervals indicates the reversal of atrend, and therefore an opportunity to trade.

For example, if the shorter term moving average crosses the longer term from thebottom, then this would indicate a buy signal, and a crossing from the oppositedirection would indicate a sell signal. There are, of course, many limits tothis.

First - and by definition - the moving average looks at past price data. Theunderlying assumption is that the chart data for a given asset is, in itself, apredictor of upcoming chart data. Another way to frame that is as a projectedprice path.

In addition, this data is lagging by definition and therefore the indicatorwould lag as well. The counterpoint to these arguments ontechnical analysisis that it can sometimes explain price action on assets that don't have muchunderlying value other than that created through speculation. Whether this is agood indicator or not is outside of our scope, but we thought it would be goodto mention some context.

From here, we can build an indicator by adding another moving average to ourinitial query with a different period. We will make these two periods variableby using Grafana variables:

SELECTtimestampAStime,
       symbol,
       price,
AVG(price)OVER(
ORDERBYtimestamp
            RANGE'$DurationOne' $FrameOnePRECEDING
)AS mAvgOne,
AVG(price)OVER(
ORDERBYtimestamp
            RANGE'$DurationTwo' $FrameTwoPRECEDING
)AS mAvgTwo
FROM trades
WHERE $__timeFilter(timestamp)
AND   symbol= $Pairs

Window functions in QuestDB

Before we dig deeper, a quick explanation of window functions for those who maybe unfamiliar. In QuestDB, a window function is defined by anOVER clause thatfollows the window function. This clause includes three components:partitioning, ordering and framespecification.

ThePARTITION BY clause divides the result set into partitions (groups ofrows) upon which the window function is applied. TheORDER BY clause withintheOVER clause determines the order of the rows in each partition. The framespecification defines the set of rows included in the window, relative to thecurrent row.

Window functions in QuestDB are often used in analytics for tasks such ascalculating running totals or averages, finding the maximum or minimum value ina sequence or partition, ranking items within a specific category or partition,and calculating or cumulative sums. And, of course, to calculate movingaverages.

Extracting a signal

Having built this query, we can now use a subquery to extract the signal of whenthe values are crossing and then overlay it on our charts.

WITHdataAS(
SELECTtimestampAStime,
           symbol,
           price,
AVG(price)OVER(
ORDERBYtimestamp
               RANGE'$DurationOne' $FrameOnePRECEDING
)AS mAvgOne,
AVG(price)OVER(
ORDERBYtimestamp
               RANGE'$DurationTwo' $FrameTwoPRECEDING
)AS mAvgTwo
FROM trades
WHERE $__timeFilter(timestamp)
AND   symbol= $Pairs
)
SELECTtime,
       symbol,
(mAvgOne- mAvgTwo)AS difference
FROMdata

The line shows the difference between the two moving averages. When thisdifference crosses zero, then could a strong signal. We therefore need totransform the analog line above into two pieces of binary information:

• A trade action signal (do nothing or trade)
• A direction signal (buy or sell)

The direction is simple: if the difference between the short term and the longterm moving averages is positive, then the signal should be 'buy'. Otherwise, itshould be sell. Shown with aCASE function to output-1 if the strategyoutput is a short position and1 if long.

WITHdataAS(
SELECTtimestampAStime,
           symbol,
           price,
AVG(price)OVER(
ORDERBYtimestamp
               RANGE'$DurationOne' $FrameOnePRECEDING
)AS mAvgOne,
AVG(price)OVER(
ORDERBYtimestamp
               RANGE'$DurationTwo' $FrameTwoPRECEDING
)AS mAvgTwo
FROM trades
WHERE $__timeFilter(timestamp)
AND   symbol= $Pairs
)
SELECTtime,
       symbol,
CASEWHEN(mAvgOne- mAvgTwo)>0THEN1
ELSE-1
ENDAS direction
FROMdata

We can also vary the frequency, for example with shorter moving averageintervals. Unsurprisingly, for the same data, it results in a higher frequencyof trades:

Lastly, we need to extract the moment we should trade. To do this, we can checkfor the moment that the direction signal changes by running an average queryover two rows. If the average is zero, then the two surrounding values are ofopposite signs (1 and-1). We can then conclude there was a change in thedirection signal and should therefore make a trade.

WITHdataAS(
SELECTtimestamp,
           symbol,
AVG(price)OVER(
ORDERBYtimestamp
               RANGE'$DurationOne' $FrameOnePRECEDING
)AS mAvgOne,
AVG(price)OVER(
ORDERBYtimestamp
               RANGE'$DurationTwo' $FrameTwoPRECEDING
)AS mAvgTwo
FROM trades
WHERE symbol= $Pairs
AND   $__timeFilter(timestamp)
)
SELECT  ts,
CASE
WHEN abs(averageCheck)<=0THEN1
ELSE0
END
FROM(
SELECT ts,
AVG(Xdirection)OVER(
ORDERBY ts
ROWS1PRECEDING
)AS averageCheck
FROM(
SELECTtimestampAS ts,
               symbol,
CASE
WHEN(mAvgOne- mAvgTwo)>0THEN1
ELSE-1
ENDAS Xdirection
FROMdata
)
)

Having done this, we can vary the symbols, time frames and the window sizes forboth moving averages to try different combinations.

Strategic profits and losses

Does the strategy make money? Or perhaps more importantly, does it make goodrisk-adjusted returns? Probably not, but we can check by adjusting the previousquery to simulate a trade at the last price whenever the signal is triggered.While this is not an efficient way of doing this, it's fun!

And hey, in this very scenario, we could have made 60 bucks… If we traded over50 times, or over 110K USD notional. Guess we'll need to find another strategy!

Summary

In this article, we explored the concept of moving averages as a trading signalusing QuestDB queries in a Grafana dashboard, with Coinbase crypto market dataas our source. We discussed how moving averages, a form of technical analysis,can indicate potential trading opportunities when two averages over differenttime intervals cross. We then demonstrated how to build an indicator by addinganother moving average to our initial query with a different period, and how toextract a signal when these values cross.

We also discussed how to determine the direction of the trade (buy or sell)based on the difference between the short term and the long term movingaverages, and how to identify the moment to trade by checking for the momentthat the direction signal changes by running an average query over two rows.

Finally, we touched on the strategy's profitability and risk-adjusted returns,and demonstrated how to simulate a trade at the last price whenever the signalis triggered. Have fun and good luck!

Checkout our real time (crypto dashboard)[/dashboards/crypto] for furtherinspiration.