Optimize Your Workflow with Scheduled Programs

For a previous project, I created a function that saved the “End Of Day” values for stock and option prices into a user table. This useful function had applications beyond my original analysis, and I had multiple coworkers ask for the data that I had produced. One of them suggested that I run this function daily so they would have data that was up to date. Instead of manually running this program every day, I turned the query into a Deephaven Persistent Query, or PQ for short. This took a few clicks, and now my data is automatically updated daily and can be used by the entire team.

What is a Persistent Query?

A PQ is a program that users can schedule a specific run time. Giving a program a set time to run enables it to have data ready to view or analyze at a set time, without any additional effort on the user's part.

There are two overarching types of PQs, continuous and discrete:

A continuous PQ can be used to run a program all day and have the data always accessible. This allows users to quickly pull up the results of their program without having to run the program and wait for results. This is a very helpful feature within the Deephaven database for multiple reasons. A few of these reasons are to have a dashboard that is always ready to be viewed, saving time by not having to rerun programs with long run times, and data that updates dynamically.
A discrete PQ runs to completion and then stops. For example, it can run a program at a set time every day with the goal of analyzing new data. By scheduling my “End Of Day” function to run once a day to calculate and save the new end of day data from that day essentially creates a program that self-updates its saved data. This feature in the Deephaven database prevents users from having to manually run their code whenever there is new data available.

Creating a Persistent Query

My original “End Of Day” function required the user to input a start and end date. Then the function would analyze all days from the start date to the end date. To convert this into a PQ, I changed the start date and end date to the current date using the currentDay() function. Now when my function runs at the scheduled time, it will analyze and update the data for the current day.

After the code changes, I configured the PQ by clicking PQ+ icon in the top right of the file viewer. This pulls up the PQ editor where I can name the PQ, set the heap size, schedule run times, and much more. After configuring the appropriate options, I click Save to create the PQ.

The most important part of making a PQ is setting the correct scheduling time. By default, a PQ will run daily, stopping overnight and restarting each morning. However there are various scheduling options to choose from from. In the case of my "End of Day" function, I specified a "Daily" schedule type and used the sliders to adjust the time as needed. Since the stock market closes at 4:00 EST, I set my PQ to run from 4:01 EST to 4:30 EST on weekdays. How I scheduled my PQ can be seen below:

See my full PQ script below:

from deephaven import *
from deephaven import QueryScope
from deephaven.DBTimeUtils import convertDateTime

calendar = cals.calendar("USNYSE")

def endOfDayTableMaker(options, stocks, start, end):
    for day in calendar.businessDaysInRange(start, end):
        QueryScope.addParam("day", day)
        QueryScope.addParam("time", convertDateTime(day + "T16:00:00 NY"))
        if db.t("Correlation", "EndOfDay").where("Date = day").size() == 0:
            stocksEOD = stocks.where("Date = day", "Timestamp < time", "AskSize != 0", "BidSize != 0"). \
                lastBy("LocalCodeStr"). \
                updateView("UnderlyingStockPrice = (AskSize*Bid+BidSize*Ask)/(AskSize+BidSize)")
            db.replaceTablePartition("Correlation", "StocksEndOfDay", day, stocksEOD)
            optionsEOD = options.where("Date = day", "Timestamp < time", "AskSize != 0", "BidSize != 0"). \
                lastBy("LocalCodeStr"). \
                naturalJoin(stocksEOD, "USym = LocalCodeStr", "UnderlyingStockPrice"). \
                updateView("OptionsPrice = (AskSize*Bid+BidSize*Ask)/(AskSize+BidSize)"). \
                view("Date", "LocalCodeStr", "USym", "Expiry", "OptionsPrice", "PutCall", "UnderlyingStockPrice", "Strike")
            db.replaceTablePartition("Correlation", "EndOfDay", day, optionsEOD)
    return
    
startDay = calendar.currentDay()
endDay = calendar.currentDay()

options = db.t("FeedOS", "OPRAQuoteL1")
stocks = db.t("FeedOS", "EquityQuoteL1")

endOfDayTableMaker(options, stocks, startDay, endDay)

The Panels Menu

I mentioned that my co-workers wanted to take advantage of my PQ. hey can assess this data by calling my saved user table, and then use it for their own calculations. In this case, my data can be called with: db.t(“Correlation”, “EndOfDay”).

Once a PQ is saved, it is also accessible to any user with the appropriate permissions from the Panels menu. Below, two tables and a plot associated with my program are added to a simple Dashboard.

Summary

Persistent queries have a variety of applications and are one of the core features that empower Deephaven users. By creating a self-updating PQ using the currentDay() method, I saved my coworkers and myself the pain of manually rerunning a query and waiting for results. Every morning, we now have the latest data available to get off and running. As you can see, setting up a PQ takes seconds and they have a variety of applications.

Last Updated: 16 February 2021 18:06 -04:00 UTC Deephaven v.1.20200928 (See other versions)