Interactive Brokers Google Sheets

Receive almost any data from IBKR right inside Google Sheets




DYI portfolio tracker, dividends calendar, trades profitability calculation, ...



Step 1: Enable Flex Web Services under Settings menu, copy token

enable flex web services

Step 2: Create Flex Query under Performance & Reports menu

create flex query

Step 3: Create Google Sheet with App Script

create google app script

Paste code and deploy



New function should appear



You may want to:

  • get additional data from finviz, yahoo finance, ...
  • build dividends calendar
  • ...


 * IBRK Flex Query.
 * From IBKR side you need enable flex service - this can be done in "Flex Web Service" in settings menu.
 * While enabling flex service - copy your token.
 * After enabling flex service go to "Flex Queries" under "Performance & Reports" menu and create one.
 * Each created report will have its own query id - copy it as well.
 * Keep in mind that this function will grab only first section of report, so if you want multiple sections just create multiple queries.
 * The report retrieval is done in two steps:
 * - step 1: request report generation, requires token and query id, will return report code and url
 * - step 2: retrieve generated report
 * Notes:
 * - when you prepared your report it is a good idea to enable cache so everything will work much faster
 * - if report is really huge try to increase to something like 5 seconds
 * @param {number} token Token from Settings \ Flex Services menu.
 * @param {number} query Query ID of Flex Query from Reports \ Flex Query menu.
 * @param {bool} headers Add header names to result. Default TRUE.
 * @param {bool} cache Cache results for 6 hours which is maximum for Google Sheets. Default FALSE.
 * @param {number} sleep Report generation takes some time, so we are waiting given amount of milliseconds before proceeding. Default is 2000 which is 2 seconds.
 * @return Query results.
 * @customfunction
function IBKRFLEXQUERY(token, query, headers=true, cache=false, sleep=2000) {
  // Step 0: optional, return cached results
  var cacheService = CacheService.getDocumentCache();
  var cacheKey = token + "." + query;
  if (cache === true && cacheService.get(cacheKey)) {
    return JSON.parse(cacheService.get(cacheKey));

  // Step 1: request report generation
  var url = ""+token+"&q="+query;
  var xml = UrlFetchApp.fetch(url).getContentText();
  var root = XmlService.parse(xml).getRootElement();
  var code = root.getChild("ReferenceCode").getValue();
  url = root.getChild("Url").getValue();

  // Step 2: retrieve generated report
  url = url + "?v=3&t="+token+"&q=" + code
  xml = UrlFetchApp.fetch(url).getContentText();
  var nodes = XmlService.parse(xml).getRootElement().getChild("FlexStatements").getChild("FlexStatement").getChildren()[0].getChildren();

  var items = [];
  for(var node of nodes) {

    // optional: add headers
    if (items.length === 0 && headers) { items.push(node.getAttributes().map(attr => attr.getName())); }

    var item = [];
    for(var attr of node.getAttributes()) {
      var text = attr.getValue();
      if (text.match(/^-?[\d,\.]+$/)) {
      } else {

  // Step 0: optional, store results in cache for subsequent requests
  if (cache === true) {
    cacheService.put(cacheKey, JSON.stringify(items), 21600);

  return items;

How it works

Two step process:

  1. Send request asking to generate report
  2. Retrieve generated report

Suppose we have an query with id 866660


curl -X POST \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "q=$QUERY_ID&v=3&t=$TOKEN" \

Response will be something like

<FlexStatementResponse timestamp='14 October, 2023 03:57 AM EDT'>

From response we will receive url and reference code to download generated report

curl -X POST \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "q=5045169185&v=3&t=$TOKEN" \


  • depending on report size it may require some time for generation to complete
  • url is different and taken from response
  • instead of query id we passing reference code

Sample response

<FlexQueryResponse queryName="portfolio" type="AF">
  <FlexStatements count="1">
    <FlexStatement fromDate="2023-10-13" toDate="2023-10-13" period="LastBusinessDay" whenGenerated="2023-10-14 03:57:57">
        <OpenPosition symbol="VZ" position="10" />
        <OpenPosition symbol="XRX" position="4" />


  • just for simplicity have removed everything, in your flex query you may add ass many fields as you want, aka prices, p&l, market value, price change and so on
  • with flex query you may receive not only positions but also trades history, dividends, taxes and many many more
  • flex queries are not supposed to receive input parameters so if you need YTD data just set it while creating query