Real-Time Stock and Currency Tracking in LibreOffice Calc

LibreOffice Calc allows users to automatically fetch live financial data, including stock prices and currency exchange rates, directly into spreadsheets. By utilizing built-in web queries or data import functions like WEBSERVICE, you can build an automated, self-updating dashboard to monitor market movements and portfolio values without manual data entry.

Method 1: Linking to External Web Data

The most common way to import dynamic stock and currency tables is using Calc’s native external data link feature.

  1. Find a Reliable Data Source: Locate a financial website or an API endpoint that provides stock or currency tables in HTML or CSV format (e.g., financial news portals or market index sites).
  2. Open External Data Settings: In LibreOffice Calc, navigate to the top menu and select Sheet > Link to External Data… (or Insert > Link to External Data… depending on your version).
  3. Insert the URL: Paste the URL of the webpage containing the financial data into the URL of External Data Source box and press Enter.
  4. Select the Data Table: In the dialog box that appears, select the specific table or named range containing the stock or currency values.
  5. Set the Update Frequency: Check the box labeled Update every and specify the refresh interval in seconds (for example, 60 seconds for minute-by-minute updates).
  6. Apply: Click OK. The table will populate in your sheet and automatically refresh at the specified interval.

Method 2: Using the WEBSERVICE and FILTERXML Functions

For retrieving specific, single-point data (like the exact price of an individual stock or currency pair), you can query REST APIs directly using built-in Calc formulas.

  1. Obtain an API Endpoint: Sign up for a free financial API service (such as Alpha Vantage, Finnhub, or ExchangeRate-API) to receive an API URL that outputs data in XML or JSON format.

  2. Fetch the Raw Data: Use the WEBSERVICE function to retrieve the data payload:

    =WEBSERVICE("https://api.example.com/quote?symbol=AAPL&apikey=YOUR_KEY&format=xml")

  3. Extract Specific Values with FILTERXML: Wrap the WEBSERVICE formula inside FILTERXML to parse the exact value using an XPath query:

    =FILTERXML(WEBSERVICE("https://api.example.com/quote?symbol=AAPL&apikey=YOUR_KEY&format=xml"), "//price")

  4. For Currency Pairs: Apply the same structure using a currency endpoint:

    =FILTERXML(WEBSERVICE("https://api.exchangerate-api.com/v4/latest/USD"), "//rates/EUR")

Enabling Automatic Updates and Security Settings

To ensure your dynamic sheet updates smoothly every time you open the document: