Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Is there a way to retrieve stock quotes using Airtable?

6846 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Stan_Z
5 - Automation Enthusiast
5 - Automation Enthusiast

Is it possible to enter a stock symbol such as AAPL and get the price update as is offered in other spreadsheet programs?

Thanks

19 Replies 19

In Airtable’s Example Script Showcase, there is an Airtable script that is created to retrieve stock quotes from financialmodelingprep.com.

The actual script is located here:

Tim_Beyers
5 - Automation Enthusiast
5 - Automation Enthusiast

@ScottWorld I may be missing something but I get the prices listed as “undefined.” (See attached.) Have you or anyone else run into this before?

Screen Shot 2020-12-20 at 3.58.46 PM

Not sure. I don’t know Javascript. It’s very possible that the script might be broken. Hopefully, one of the JavaScript experts in the forum can help. Maybe try reaching out to Airtable support, too, since it’s one of their scripts. Their email is support@airtable.com.

Thanks! I sent a note to the team.

Tony_Edmonds
4 - Data Explorer
4 - Data Explorer

I’ve had the same problem.

I tried an alternative script below, but this actually clears the current price field in my base rather than recording the quote value. If I copy the URL into a browser window, it returns the ticker and price.

Any idea how to capture the price returned from API and update the price field in the base? I’m sure its a notation issue as the URL will return the data in a normal browser window…

let tblStocks = base.getTable(“stk_price”);

let fldTblStocks_symbol = tblStocks.getField(“symbol”);
let fldTblStocks_currentValue = tblStocks.getField(“curr_price”);

let result = await tblStocks.selectRecordsAsync();

for (let record of result.records) {

let stkSymbol = record.getCellValue (fldTblStocks_symbol);

let response = await fetch ('https://financialmodelingprep.com/api/v3/stock/real-time-price/${stkSymbol}?apikey=abcMYKEY123');
let data = await response.json();

if (data) {
    console.log(stkSymbol);
    console.log(data);
   await tblStocks.updateRecordAsync(record, {
       [fldTblStocks_currentValue.id]: data.price
   });
}

}

Cameron_Cooper1
4 - Data Explorer
4 - Data Explorer

This would be an amazing add-on. Hoping Airtable would want to create something like this.

Use the character ` instead of ’ for your fetch statement:

let response = await fetch (`https://financialmodelingprep.com/api/v3/stock/real-time-price/${stkSymbol}?apikey=abcMYKEY123`);

This will allow ${stkSymbol} to be replaced with the actual value.

derek_haefner
4 - Data Explorer
4 - Data Explorer

I created a script to add the current price of a given stock symbol to a specific field. The endpoint it hits is free and doesn’t need any configuration so should be straight forward to use but if not just let me know!

let config = input.config({
    title: 'Current Stock Price Settings',
    description: 'Get the current price of a stock.',
    items: [
        input.config.table('selectedTable', {
            label: 'Table to use',
            description: 'Pick any table in this base!',
        }),
        input.config.field('selectedSymbolField', {
            label: 'Field inside the above table where the stock symbol is',
            parentTable: 'selectedTable',
        }),
        input.config.field('selectedPriceField', {
            label: 'Field inside the above table where the current price should be shown',
            parentTable: 'selectedTable',
        }),
    ]
});

const table = config.selectedTable;
const { records } = await table.selectRecordsAsync({
    fields: [config.selectedSymbolField.name]
});
const priceField = config.selectedPriceField.name;

let itemsToUpdate = [];
const badStockSymbols = [];

// Loop through all stock symbols, get the price, and create the update objects
await Promise.all(records.map(async (record, index) => {
    // Don't look up empty records
    if (!record.name || record.name === `Unnamed record`) {
        return;
    }

    try {
        const response = await remoteFetchAsync(`https://query1.finance.yahoo.com/v8/finance/chart/${record.name}`);
        const { chart } = await response.json();

        if (chart?.error) {
            badStockSymbols.push(record.name);
            return;
        }

        itemsToUpdate.push({
            id: record.id,
            fields: {
                [`${priceField}`]: chart?.result[0]?.meta?.regularMarketPrice
            }
        })

        output.clear();
        output.markdown(`Fetched ${(((itemsToUpdate.length + badStockSymbols.length) / records.length) * 100).toFixed(2)}% of stock prices.`);
        return;
    } catch (err) {
        console.log(err);
    }
}));

// Bulk update fields with the current price
while (itemsToUpdate.length > 0) {
    await table.updateRecordsAsync(itemsToUpdate.slice(0, 50));
    itemsToUpdate = itemsToUpdate.slice(50);
}

output.markdown(`Added stock prices!`);
output.markdown(`Fields with bad symbols. Count: ${badStockSymbols.length}`);
output.table(badStockSymbols);

Thanks Derek! Great to get some practical language/functionality around pulling off this integration.
I’m a noob at developing my own extensions, but can hang a little.
I’d like to modify this so it returns the Stock Symbol only from a company name field. Anyone have insights into pulling that off?

Thank you for taking the time to read!

-Mike

Hi Michael! For example, you want it to take a company field that could be google and return GOOGL. Is that correct?