Convert RBC Royal Bank statements to Excel

How far back RBC eStatements go

RBC's statement options page and eStatements page set out a few rules worth knowing before you need an old statement:

• eDocuments are kept for up to 7 years in Online Banking; some custom statements only go back 18 months.
• eStatements exist only for accounts switched to eStatements, so months you received on paper may be missing online.
• Once an account is closed, you can't access its eDocuments any more. If you're closing an account, download its statements first.
• The mobile app shows the last 6 statements automatically; use Online Banking on a computer for anything older.
• Paper statements carry a monthly fee, and printed copies at a branch or ATM are charged per statement, according to RBC's eStatements page.

Can you download RBC transactions as CSV?

RBC Online Banking includes a transaction download, but RBC's help pages don't publish which file types it offers or how far back it goes. If you do get a CSV or QFX file from RBC that covers the dates you need, upload that instead of PDFs — downloaded files use no pages from a plan or page pack, and you can still export them to QuickBooks or Excel with categories.

For anything the download doesn't reach, for a client's statements and for a closed account whose statements you saved, the eStatement PDF is the source. It's also the document with the opening and closing balance, which is what proves a set of transactions is complete.

What an RBC statement looks like

RBC's sample eStatement shows the layout: a period heading such as "From March 12 to April 12", an opening balance, then columns for date, description, withdrawals, deposits and balance. The date is printed once per day without a year ("15 Mar"), and the balance appears only on the last transaction of each day.

Two things follow. First, statement periods don't line up with calendar months, so when you combine several statements, use the export's date range to cut out exactly the months you need. Second, rows without a printed date belong to the day above. The conversion fills in the date and year on every row, merges withdrawals and deposits into one signed amount, and checks the daily balances wherever they're printed.

RBC credit card statements

Credit card statements are in the same place: Statements/Documents, then the View eDocuments tab and Search eDocuments (RBC instructions). They're kept for seven years. Convert them like bank statements: purchases, fees and interest come out negative and payments positive, and each statement is checked from its previous balance to its new balance. See how card statements convert to Excel.

RBC statements into QuickBooks

• QuickBooks Online: export the QuickBooks Online CSV — Date, Description, Amount — and upload it to the bank or card account. The upload screen is explained in importing bank statements into QuickBooks Online.
• QuickBooks Desktop: export a .qbo Web Connect file.
• Other software: OFX for Wave and Sage, QIF for Quicken, or a Xero CSV — see bank statements to QuickBooks for how the formats compare.
• Spreadsheets: Excel with a Transactions sheet and a Summary sheet by category and month.

Statements in French convert the same way, so a Québec client's relevés can go into the same export as English statements.

Catching up a year of RBC statements

Bookkeepers catching up a client and anyone preparing a tax return usually need twelve statements at once. Upload them together, check that each one balances, and export one date-ordered file limited to the calendar or fiscal year. The Summary sheet totals money in and out by category and month, so the year-end figures are already there. Other banks are covered in all bank guides.

Questions