Getting Started

To start using the Roic AI API, you’ll need an API key which can be obtained on the Settings page by registering on our website and subscribing to a Professional plan.

The API and Excel plugin are available only on the Professional plan.

Authentication

To authenticate your API requests, include your API key in the query of each call:

https://api.roic.ai/v1/rql/?query=...&apikey=637dsjx15354323940830387c1524100c0

Pandas Dataframes

To easily import data into a pandas dataframe, you can use the dataformat=csv parameter.

Just append &dataformat=csv to the end of your query.

Example output:

fiscal_year,2023\nperiod_label,2023\nperiod_end_date,2023-09-30\nis_sales_revenue_turnover,383285000000.00000000\n

Google Sheets Add-on

We've tried very hard to get past the review process for the Google Sheets add-on. But this company is too sluggish and we're still waiting for the review to finish.

For now, you can use the custom Google Script.

Installation

Video version

https://youtu.be/rDEZyeCeyy4youtu.be

Text version:

1. Open Google Sheets and Create a New Spreadsheet:

   • Navigate to Google Sheets.

   • Click on "Blank" or the "+" icon to start a new spreadsheet.

2. Access the Extensions Menu:

   • In the new version of Google Sheets, click on the “Extensions” menu in the toolbar.

   • Select “Apps Script” from the dropdown menu.

3. Set Up Your Apps Script Project:

   • A new tab will open with the Google Apps Script editor.

   • The script will be automatically linked to your Google Spreadsheet.

4. Insert the Custom Function Code:

   • Copy the code for your =RQL function.

   • Paste this code into the script editor, replacing any existing placeholder script.

5. Save and Name Your Script:

   • Click the floppy disk icon or select "File" > "Save" to save your script.

   • Enter a name for your script project if prompted.

6. Use the =RQL Function in Your Spreadsheet:

   • Return to your Google Spreadsheet.

   • In a cell, start typing =RQL() and fill in the necessary parameters to execute your custom function.

Google Script

Do not forget to replace `YOUR_API_KEY` with your actual api key in the script. You can find it on the Settings page.

/**
 * RQL - Roic AI Query Language - Fetch fundamental data for a given security.
 *
 * @param {string} ticker - The security identifier of the company. Should include the exchange code. Ex. "AAPL US"
 * @param {string} field - The field to be fetched. Ex. is_sales_revenue_turnover
 * @param {string} parameter_1 - The first parameter to be passed to the field. Ex. showdates=true
 * @param {string} parameter_2 - The second parameter to be passed to the field. Ex. showheaders=true
 * @param {string} parameter_3 - The third parameter to be passed to the field. Ex. transpose=true
 * @param {string} parameter_4 - The fourth parameter to be passed to the field. Ex. xlsort=ASC
 * @param {string} parameter_5 - The fifth parameter to be passed to the field. Ex. showquery=true
 * @return The asked information from the fund, according to the selected source.
 * @customfunction
 */
function RQL(ticker, field, parameter_1, parameter_2, parameter_3, parameter_4, parameter_5) {
    if (!ticker && !field) {
        throw new Error("Please provide a security identifier and a field");
    }
    if (!ticker) {
        throw new Error("Please provide a security identifier");
    }
    if (!field) {
        throw new Error("Please provide a field");
    }
    var query = "get(".concat(field, ") for('").concat(ticker, "')");
    // Compose query parameters
    var queryParams = [
        parameter_1,
        parameter_2,
        parameter_3,
        parameter_4,
        parameter_5,
    ]
        .filter(function (param) { return param; })
        .map(function (param) { return "&".concat(param); })
        .join("");
    // Construct the URL
    var url = "https://api.roic.ai/v1/rql/?query=".concat(query).concat(queryParams, "&apikey=YOUR_API_KEY");
    var fetch = UrlFetchApp.fetch(url);
    if (fetch.getResponseCode() == 200 && fetch.getContent().length > 0) {
        var body = fetch.getContentText();
        var json = JSON.parse(body);
        return json;
    }
    else {
        throw new Error("Wrong combination of asset identifier and source. Please check the accepted ones at the documentation.");
    }
}