Extracting transactions from RBC Personal Online Banking OMNI

0_README.md

RBC Royal Bank Online Banking doesn't allow extracting CSV of transactions

This does. Just run this when you’ve selected the filter criteria you want, then paste this in developer tool. It'll transform the transaction button with "Download CSV".

A date range and you want those transactions as CSV, with transaction number and only amounts, no formatting: so you can use your spreadsheet to manage that data.

Furthermore, RBC’s OMNI Team; What would be even cooler is if when we filter the transactions with criterias. When we click to view an item, like a Cheque image, when we click "Back" on the browser, we don’t lose the search we had. Like it currently does. That would be easily possible if the URL kept the filter criterias. This changes browser history, and allows to use the Back button *for free*. But the FrontEnd code doesn’t do that. And that Gist’s content isn't covering this problem — But I might tackle that one too some day.

Published at:

• https://gist.github.com/renoirb/f865666be869f836ec567f22b3719827
• https://twitter.com/renoirb/status/1583848533828202500?s=20&t=alsfgIvR3lR3zNt3u0ikvA

Illustration

In action

RBC-Download-Transactions.mp4

extracting_transactions.js

/**
 * Extracting transactions.
 *
 * Making the "Download" to actually work with the filter. 
 * Not send to the legacy online banking that limits only to some unclear date range.
 */

// Convert coma as cents separator back to a dot, so Spreadsheet software treats this appropriately
var normalizingAmount = (amount) =>
  amount
    .replace(/\$/, '' /* No need for dollar sign */)
    .replace(/-/, '' /* No need for negative sign */)
    .replace(/\s/g, '' /* No need for extraneous space */)
    .replace(
      ',',
      '.' /* CSV and tables always uses dot for cents. Même quand on parle français. */,
    )

var goodDateFormatOrThrow = (date) => {
  if (!/^\d\d\d\d-\d\d-\d\d/.test(date)) {
    const message = `Invalid date format "${date}". We expected the date string to match ISO8601 date (e.g. YYYY-mm-dd, e.g. 1st of june 2022 be 2022-06-01), because in CSV textual format does not make sense!`
    throw new Error(message)
  }
  return date
}

var transformDownloadButton = (a, content) => {
  if ('nodeName' in a && a.nodeName === 'A') {
    const blob = new Blob([content], { type: 'text/csv' })
    const url = URL.createObjectURL(blob)
    a.setAttribute('href', url)
    a.setAttribute('download', 'transactions.csv')
    a.innerHTML = '<span>✨ Download CSV</span>'
  } else {
    const message = `Something unexpected occured. We expected to have an anchor <a/> element`
    throw new Error(message)
  }
}

var extractFromTransactionListTable = (transactionListTable) => {
  if (
    'nodeName' in transactionListTable &&
    transactionListTable.nodeName === 'TABLE'
  ) {
    var transactionRows = Array.from(
      transactionListTable.querySelectorAll('tbody tr'),
    )

    var rows = ['"Date","Description","Transaction","Debit","Credit","Total"']

    for (const row of transactionRows) {
      const rowNodeList = row.querySelectorAll('td')
      const [dateCell, descCell, dtCell, ctCell, totalCell] = rowNodeList
      // The id attribute is supposed to exist only once on any node of a web page.
      // That is because IDs should be unique. Here, we have a TD (Table Data... a Cell)
      // with the same ID for every row of transaction (i.e. same date).
      // What would be better is to add a datetime attribute on a time element inside the TD
      // https://developer.mozilla.org/en-US/docs/Web/HTML/Element/time#attr-datetime
      const date = dateCell.getAttribute('id')
      //                                  ^^
      goodDateFormatOrThrow(date)
      let desc = descCell.textContent.trim() ?? ''
      // Grab the transaction number from the description. Useful for matching when transfering between accounts
      const txNbr = /\s-\s(\d+)/.exec(desc)?.[1] ?? ''
      const dtBefore = dtCell.textContent.trim() ?? ''
      const ctBefore = ctCell.textContent.trim() ?? ''
      const totalBefore = totalCell.textContent.trim() ?? ''
      const dt = normalizingAmount(dtBefore)
      const ct = normalizingAmount(ctBefore)
      const total = normalizingAmount(totalBefore)
      const rowAsText = `"${date}","${desc}","${txNbr}","${dt}","${ct}","${total}"`
      rows.push(rowAsText)
    }
    return rows
  } else {
    const message = `Something unexpected occured. We expected to find a table element and we did not.`
    throw new Error(message)
  }
}

;(() => {
  var table = document.querySelector('table.rbc-transaction-list-table')
  const rows = extractFromTransactionListTable(table)
  var [a] = document.querySelectorAll(
    '[rbcportalsubmit="DownloadTransactions"]',
  )
  transformDownloadButton(a, rows.join('\n'))
})()

Thanks for the starting point @renoirb! I ran into the same issues that @ghost did, and I noticed a couple of issues with the script. Likely RBC changed the DOM structure slightly.

• When viewing credit card transactions, there's can be upwards of 4 tables on the page. Posted transactions vs. pending transactions, and mobile friendly vs. desktop friendly. I've modified the script to ignore the mobile friendly tables and to process both pending and posted transactions.
• For whatever reason, transactionListTable.querySelectorAll('tbody tr') was including the header row, which is what was causing the issue. I've updated the selector to ignore the header row.
• There was issues with replacing the download button, so instead there's a prompt that will open when you paste in the JS, and accepting the prompt will automatically download the file. The filename will be %{ISO8601 date}-${parameterized page title}.csv
• Your comment about duplicate element IDs is correct when viewing credit card transactions, but for bank account transactions the element ID is row-1-2025-06-30. I've updated the JS to properly extract the date for both formats.

I also asked Gemini to convert it to a bookmarklet to balance usability with the desire to be able to audit random Javascript you find on the internet executing in your banking website... 😅

javascript:(function(){var normalizingAmount=(amount)=>amount.replace(/\$/, '').replace(/-/, '').replace(/\s/g, '').replace(',', '.');var extractDate=(date)=>{const dateFormat=/(\d{4}-\d{2}-\d{2})/;const extractedDate=date.match(dateFormat)?.[1];if(!extractedDate){const message=`Invalid date format "${date}". We expected the date string to match ISO8601 date (e.g.YYYY-MM-DD, e.g. 1st of june 2022 be 2022-06-01), because in CSV textual format does not make sense!`;throw new Error(message);}return extractedDate;};var parameterize=(str)=>{if(!str)return'';str=str.trim();str=str.toLowerCase();str=str.replace(/[^a-z0-9 -]/g,'');str=str.replace(/\s+/g,'-');str=str.replace(/-+/g,'-');str=str.replace(/^-+|-+$/g,'');return str;};var downloadContent=(content,filename="data.csv")=>{const blob=new Blob([content.join("\n")],{type:"text/csv;charset=utf-8;"});const url=URL.createObjectURL(blob);const link=document.createElement('a');link.setAttribute('href',url);link.setAttribute('download',filename);document.body.appendChild(link);link.click();document.body.removeChild(link);URL.revokeObjectURL(url);};var extractFromTransactionListTable=(transactionListTable,rows)=>{if('nodeName'in transactionListTable&&transactionListTable.nodeName==='TABLE'){var transactionRows=transactionListTable.querySelectorAll('.rbc-transaction-list-transaction-new');console.log("Processing table with",transactionRows.length,"rows");transactionRows.forEach(row=>{const rowNodeList=row.querySelectorAll('td');const[dateCell,descCell,dtCell,ctCell,totalCell]=rowNodeList;const date=extractDate(dateCell.getAttribute('id'));let desc=descCell.textContent.trim()??'';const txNbr=/\s-\s(\d+)/.exec(desc)?.[1]??'';const dtBefore=dtCell.textContent.trim()??'';const ctBefore=ctCell.textContent.trim()??'';const totalBefore=totalCell.textContent.trim()??'';const dt=normalizingAmount(dtBefore);const ct=normalizingAmount(ctBefore);const total=normalizingAmount(totalBefore);const rowAsText=`"${date}","${desc}","${txNbr}","${dt}","${ct}","${total}"`;rows.push(rowAsText);});return rows;}else{const message=`Something unexpected occured. We expected to find a table element and we did not.`;throw new Error(message);}};var rows=['"Date","Description","Transaction","Debit","Credit","Total"'];document.querySelectorAll('.hide-on-mobile table.rbc-transaction-list-table').forEach(table=>{extractFromTransactionListTable(table,rows);});if(confirm(`Do you want to download a CSV with ${rows.length-1} rows?`)){filename=`${new Date().toISOString()}-${parameterize(document.title)}.csv`;downloadContent(rows,filename);}})();

/**
 * Extracting transactions.
 *
 * Making the "Download" to actually work with the filter. 
 * Not send to the legacy online banking that limits only to some unclear date range.
 */

// Convert comma as cents separator back to a dot, so Spreadsheet software treats this appropriately
var normalizingAmount = (amount) =>
  amount
    .replace(/\$/, '' /* No need for dollar sign */)
    .replace(/-/, '' /* No need for negative sign */)
    .replace(/\s/g, '' /* No need for extraneous space */)
    .replace(
      ',',
      '.' /* CSV and tables always uses dot for cents. Même quand on parle français. */,
    )

var extractDate = (date) => {
  const dateFormat = /(\d{4}-\d{2}-\d{2})/
  const extractedDate = date.match(dateFormat)[1]
  if (!extractedDate) {
    const message = `Invalid date format "${date}". We expected the date string to match ISO8601 date (e.g. YYYY-mm-dd, e.g. 1st of june 2022 be 2022-06-01), because in CSV textual format does not make sense!`
    throw new Error(message)
  }
  return extractedDate
}

var parameterize = (str) => {
  if (!str) return ''; // Handle null or empty strings

  str = str.trim(); // Trim leading/trailing whitespace
  str = str.toLowerCase(); // Convert to lowercase

  // Replace non-alphanumeric characters (except spaces and hyphens) with an empty string
  // This step removes punctuation, symbols, etc.
  str = str.replace(/[^a-z0-9 -]/g, '');

  // Replace spaces and multiple hyphens with a single hyphen
  str = str.replace(/\s+/g, '-'); // Replace spaces with a single hyphen
  str = str.replace(/-+/g, '-'); // Replace multiple hyphens with a single hyphen

  // Trim leading/trailing hyphens (important if the original string started/ended with non-alphanumeric)
  str = str.replace(/^-+|-+$/g, '');

  return str;
}

var downloadContent = (content, filename = "data.csv") => {
  const blob = new Blob([content.join("\n")], { type: "text/csv;charset=utf-8;" })
  const url = URL.createObjectURL(blob)

  // Create a temporary <a> element
  const link = document.createElement('a')
  link.setAttribute('href', url)
  link.setAttribute('download', filename)

  // Append the link to the body (required for Firefox)
  document.body.appendChild(link)

  // Programmatically click the link to trigger the download
  link.click()

  // Clean up: remove the link and revoke the object URL
  document.body.removeChild(link)
  URL.revokeObjectURL(url)
}

var extractFromTransactionListTable = (transactionListTable, rows) => {
  if (
    'nodeName' in transactionListTable &&
    transactionListTable.nodeName === 'TABLE'
  ) {
    var transactionRows = transactionListTable.querySelectorAll('.rbc-transaction-list-transaction-new')

    console.log("Processing table with", transactionRows.length, "rows")

    transactionRows.forEach(row => {
      const rowNodeList = row.querySelectorAll('td')
      const [dateCell, descCell, dtCell, ctCell, totalCell] = rowNodeList
      // The id attribute is supposed to exist only once on any node of a web page.
      // That is because IDs should be unique. Here, we have a TD (Table Data... a Cell)
      // with the same ID for every row of transaction (i.e. same date).
      // What would be better is to add a datetime attribute on a time element inside the TD
      // https://developer.mozilla.org/en-US/docs/Web/HTML/Element/time#attr-datetime
      const date = extractDate(dateCell.getAttribute('id'))
      //                                              ^^
      let desc = descCell.textContent.trim() ?? ''
      // Grab the transaction number from the description. Useful for matching when transfering between accounts
      const txNbr = /\s-\s(\d+)/.exec(desc)?.[1] ?? ''
      const dtBefore = dtCell.textContent.trim() ?? ''
      const ctBefore = ctCell.textContent.trim() ?? ''
      const totalBefore = totalCell.textContent.trim() ?? ''
      const dt = normalizingAmount(dtBefore)
      const ct = normalizingAmount(ctBefore)
      const total = normalizingAmount(totalBefore)

      const rowAsText = `"${date}","${desc}","${txNbr}","${dt}","${ct}","${total}"`
      rows.push(rowAsText)
    })
    return rows
  } else {
    const message = `Something unexpected occured. We expected to find a table element and we did not.`
    throw new Error(message)
  }
}

  ; (() => {
    var rows = ['"Date","Description","Transaction","Debit","Credit","Total"']

    document.querySelectorAll('.hide-on-mobile table.rbc-transaction-list-table').forEach(table => {
      extractFromTransactionListTable(table, rows)
    })

    if (confirm(`Do you want to download a CSV with ${rows.length - 1} rows?`)) {
      filename = `${new Date().toISOString()}-${parameterize(document.title)}.csv`
      downloadContent(rows, filename)
    }
  })()

/**
 * Extracting transactions.
 *
 * Making the "Download" to actually work with the filter. 
 * Not send to the legacy online banking that limits only to some unclear date range.
 */

// Convert comma as cents separator back to a dot, so Spreadsheet software treats this appropriately
var normalizingAmount = (amount) =>
  amount
    .replace(/\$/, '' /* No need for dollar sign */)
    .replace(/-/, '' /* No need for negative sign */)
    .replace(/\s/g, '' /* No need for extraneous space */)
    .replace(
      ',',
      '.' /* CSV and tables always uses dot for cents. Même quand on parle français. */,
    )

var extractDate = (date) => {
  const dateFormat = /(\d{4}-\d{2}-\d{2})/
  const extractedDate = date.match(dateFormat)[1]
  if (!extractedDate) {
    const message = `Invalid date format "${date}". We expected the date string to match ISO8601 date (e.g. YYYY-mm-dd, e.g. 1st of june 2022 be 2022-06-01), because in CSV textual format does not make sense!`
    throw new Error(message)
  }
  return extractedDate
}

var parameterize = (str) => {
  if (!str) return ''; // Handle null or empty strings

  str = str.trim(); // Trim leading/trailing whitespace
  str = str.toLowerCase(); // Convert to lowercase

  // Replace non-alphanumeric characters (except spaces and hyphens) with an empty string
  // This step removes punctuation, symbols, etc.
  str = str.replace(/[^a-z0-9 -]/g, '');

  // Replace spaces and multiple hyphens with a single hyphen
  str = str.replace(/\s+/g, '-'); // Replace spaces with a single hyphen
  str = str.replace(/-+/g, '-'); // Replace multiple hyphens with a single hyphen

  // Trim leading/trailing hyphens (important if the original string started/ended with non-alphanumeric)
  str = str.replace(/^-+|-+$/g, '');

  return str;
}

var downloadContent = (content, filename = "data.csv") => {
  const blob = new Blob([content.join("\n")], { type: "text/csv;charset=utf-8;" })
  const url = URL.createObjectURL(blob)

  // Create a temporary <a> element
  const link = document.createElement('a')
  link.setAttribute('href', url)
  link.setAttribute('download', filename)

  // Append the link to the body (required for Firefox)
  document.body.appendChild(link)

  // Programmatically click the link to trigger the download
  link.click()

  // Clean up: remove the link and revoke the object URL
  document.body.removeChild(link)
  URL.revokeObjectURL(url)
}

var extractFromTransactionListTable = (transactionListTable, rows) => {
  if (
    'nodeName' in transactionListTable &&
    transactionListTable.nodeName === 'TABLE'
  ) {
    var transactionRows = transactionListTable.querySelectorAll('.rbc-transaction-list-transaction-new')

    console.log("Processing table with", transactionRows.length, "rows")

    transactionRows.forEach(row => {
      const rowNodeList = row.querySelectorAll('td')
      const [dateCell, descCell, dtCell, ctCell, totalCell] = rowNodeList
      // The id attribute is supposed to exist only once on any node of a web page.
      // That is because IDs should be unique. Here, we have a TD (Table Data... a Cell)
      // with the same ID for every row of transaction (i.e. same date).
      // What would be better is to add a datetime attribute on a time element inside the TD
      // https://developer.mozilla.org/en-US/docs/Web/HTML/Element/time#attr-datetime
      const date = extractDate(dateCell.getAttribute('id'))
      //                                              ^^
      let desc = descCell.textContent.trim() ?? ''
      // Grab the transaction number from the description. Useful for matching when transfering between accounts
      const txNbr = /\s-\s(\d+)/.exec(desc)?.[1] ?? ''
      const dtBefore = dtCell.textContent.trim() ?? ''
      const ctBefore = ctCell.textContent.trim() ?? ''
      const totalBefore = totalCell.textContent.trim() ?? ''
      const dt = normalizingAmount(dtBefore)
      const ct = normalizingAmount(ctBefore)
      const total = normalizingAmount(totalBefore)

      const rowAsText = `"${date}","${desc}","${txNbr}","${dt}","${ct}","${total}"`
      rows.push(rowAsText)
    })
    return rows
  } else {
    const message = `Something unexpected occured. We expected to find a table element and we did not.`
    throw new Error(message)
  }
}

  ; (() => {
    var rows = ['"Date","Description","Transaction","Debit","Credit","Total"']

    document.querySelectorAll('.hide-on-mobile table.rbc-transaction-list-table').forEach(table => {
      extractFromTransactionListTable(table, rows)
    })

    if (confirm(`Do you want to download a CSV with ${rows.length - 1} rows?`)) {
      filename = `${new Date().toISOString()}-${parameterize(document.title)}.csv`
      downloadContent(rows, filename)
    }
  })()

You are very much right in that you should not simply execute arbitrary JavaScript in your online banking, but that's exactly what I did and your code works perfectly, so thank you.