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'))
})()

That’s cool.

I can’t run this code in RBC’s legacy overly expensive mutual funds tables. I’ve moved my money out. I’d rather pay 0.0n% instead of 1% and more and having to spend 1.5h to be able to tell where I can invest my own money.

This was used to allow me to do some analysis to see how much I have actually captured adjusting with each securities’s management fee. I’m glad it’s useful for someone else