# Guide There are two ways to build a screener: by either asking the AI or building your own query. In this document, we will focus on building your queries for advanced use cases. While AI can help, it is not perfect, so it's always a good idea to review the query it generates. ### **Pre-defined Variables** We offer over 100 fundamental filters you can use. You can find detailed descriptions of these filters by visiting the appropriate page. ### **Basic Filtering** #### **Example 1** Filter for companies with EBITDA greater than 5 million: ```json { "fundamentals": { "and": [ {"ebitda": {"gt": 5000000}} ] }, "columns": ["ticker", "fiscalperiod", "date", "ebitda"] } ``` #### **Example 2** Filter for companies with EBITDA greater than 5 million and a market cap between 1 and 5 billion: ```json { "fundamentals": { "and": [ {"ebitda": {"gt": 5000000}}, {"marketcap": {"between": [1000000000, 5000000000]}} ] }, "columns": ["ticker", "fiscalperiod", "date", "ebitda"] } ``` ### **Additional Columns** You can include additional columns in the output, such as price and volume data. However, only predefined and user-defined variables are permitted. ```json { "fundamentals": { "and": [ {"ebitda": {"gt": 5000000}}, {"marketcap": {"between": [1000000000, 5000000000]}} ] }, "columns": ["ticker", "date", "open", "high", "low", "close", "volume"] } ``` ### **Prevent browser hangs using "max\_tickers"** Handling large data sets can potentially cause your browser to hang, so it's recommended to limit the number of tickers displayed in a single result. ```json { "max_tickers": 100, "fundamentals": { "and": [ {"ebitda": {"gt": 5000000}}, {"marketcap": {"between": [1000000000, 5000000000]}} ] }, "columns": ["ticker", "date", "open", "high", "low", "close", "volume"] } ``` ### Point-in-Time Screening We have a historical dataset from January 1998 until today. You can do historical scans by using `point_in_time` parameter ```json { "point_in_time": "2023-12-01", "max_tickers": 100, "fundamentals": { "and": [ {"ebitda": {"gt": 5000000}}, {"marketcap": {"between": [1000000000, 5000000000]}} ] }, "columns": ["ticker", "date", "open", "high", "low", "close", "volume"] } ``` ### **Logical Operators** We support logical operators such as `and` and `or`. #### **Root "and" Operator** At least one `and` operator is required within the fundamentals or technicals filters. The first `and` is known as the root logical operator. ```json { "fundamentals": { "and": [] }, "daily": { "and": [] } } ``` You cannot have two or more root logical operators: ```json { "fundamentals": { "and": [], "and": [] }, "daily": { "and": [] } } ``` Instead, you should use nesting: ```json { "fundamentals": { "and": [ {"or": [ {"ebitda": {"gt": 5}}, {"revenue": {"gt": 100}} ]} ] }, "daily": { "and": [] } } ``` There are no limits on how many nested logical operators you can use, but be aware that complex queries may slow down the screener. ### **Comparison Operators** We have used the `gt` (greater than) operator so far, but other comparison operators are supported: * `eq`: equal to * `neq`: not equal to * `gt`: greater than * `gte`: greater than or equal to * `lt`: less than * `lte`: less than or equal to * `between`: within a specified range #### **Example of `eq` Operator** EBITDA is equal to 5 million: ```json { "fundamentals": { "and": [ {"ebitda": {"eq": 5000000}} ] } } ``` #### **Example of `neq` Operator** EBITDA is not equal to 5 million: ```json { "fundamentals": { "and": [ {"ebitda": {"neq": 5000000}} ] } } ``` #### **Example of `gt` Operator** EBITDA is greater than 5 million: ```json { "fundamentals": { "and": [ {"ebitda": {"gt": 5000000}} ] } } ``` #### **Example of `gte` Operator** EBITDA is greater than or equal to 5 million: ```json { "fundamentals": { "and": [ {"ebitda": {"gte": 5000000}} ] } } ``` #### **Example of `lt` Operator** EBITDA is less than 5 million: ```json { "fundamentals": { "and": [ {"ebitda": {"lt": 5000000}} ] } } ``` #### **Example of `lte` Operator** EBITDA is less than or equal to 5 million: ```json { "fundamentals": { "and": [ {"ebitda": {"lte": 5000000}} ] } } ``` #### **Example of `between` Operator** EBITDA is between 5 and 7 million: ```json { "fundamentals": { "and": [ {"ebitda": {"between": [5000000, 7000000]}} ] } } ``` ### Defining Variables You can assign a variable with a number, a mathematical expression, by aliasing a predefined variable or a combination of both. The variable name must always begin with a non-numeric character. ```json { "variables": { "fundamentals": { "alias_for_ebitda": "ebitda", "something_that_has_two": 2, "sample_math": "2 * 10", "math_with_predefined_var": "ebitda * 2" }, "daily": { "today_closed": "close" } } } ``` Variable names with number prefix is not allowed: ```json { "variables": { "fundamentals": {"2invalid_named_variable: "ebitda"} }, } ``` ### **Defining a Mathematical Formula** The following basic mathematical operations are supported:\ `+` (Addition), `-` (Subtraction), `*` (Multiplication), `/` (Division), and parentheses `()` for grouping. For example, if you want to create a custom filter for `(ebitda + revenue) * 2`, you would define a variable as follows: ```json { "variables": { "fundamentals": {"my_formula": "(ebitda + revenue) * 2"} } } ``` You can then use the variable `my_formula` inside the fundamentals filter. Sorting is also supported: ```json { "variables": { "fundamentals": {"my_formula": "(ebitda + revenue) * 2"} }, "fundamentals": { "and": [ {"my_formula": {"gt": 5}} ], "sort": {"my_formula": "desc"} }, "columns": ["ticker", "fiscalperiod", "date", "my_formula"] } ``` ## Advanced Functions ### Using the `previous` Function The `previous` function allows you to reference the value of a variable from previous rows. The syntax for the `previous` function is as follows: ```json { "variables": { "fundamentals": { "name": {"function": "previous", "args": ["var", n]} } } } ``` * **`var`**: This is the name of the variable (pre-defined or user-defined) whose previous value you want to retrieve. * **`n`**: This is the number of periods you want to look back. For example, `n=1` would retrieve the value from the previous period. #### Example 1: Getting Yesterday's Closing Price In this example, we want to get the closing price from the previous day (yesterday's closing price), and then filter for cases where yesterday's closing price was greater than 500. ```json { "variables": { "daily": { "previous_day_closed": {"function": "previous", "args": ["close", 1]} } }, "daily": { "and": [ {"previous_day_closed": {"gt": 500}} ] }, "columns": ["ticker", "fiscalperiod", "date", "previous_day_closed"] } ``` #### Example 2: Getting Last Year's Closing Price In this example, we want to get the last year's closing price, and then filter for cases where price is greater than 500 ```json { "variables": { "yearly": { "previous_year_close": {"function": "previous", "args": ["close", 1]} } }, "yearly": { "and": [ {"previous_year_close": {"gt": 500}} ] }, "columns": ["ticker", "fiscalperiod", "date", "previous_day_closed"] } ``` ### **Using the `average` Function** The average function calculates a simple moving average. Here’s how it is structured: ```json { "variables": { "fundamentals": { "name": {"function": "average", "args": ["var", n]} } } } ``` * **var**: Represents the name of the variable (either pre-defined or user-defined) that you want to calculate the average for. * **n**: Specifies the number of periods over which to calculate the average. For example, `n=5` would compute a 5-day moving average for a stock. * **t** (optional): Can define the time frame such as daily, weekly, monthly, or yearly. **Example 1: 200-day Moving Average of the Closing Price** In this case, the goal is to calculate the 200-day moving average of a stock's closing price. ```json { "variables": { "daily": { "200_day_sma": {"function": "average", "args": ["close", 200, "day"]} } }, "daily": { "and": [ {"200_day_sma": {"gt": "close"}} ] }, "columns": ["ticker", "date", "close", "200_day_sma"] } ``` This example computes the 200-day simple moving average (SMA) of the closing price and then checks if the 200-day SMA is greater than the current closing price. ## **Getting Help with Queries from the AI Assistant** If you're having trouble correcting a query or are unsure of the filter name, you can always ask the AI assistant for help. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://ratteer.gitbook.io/ratteer-docs/getting-started/guide.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.