Turn a bank statement into transaction rows

Upload one bank statement PDF, run it, and every posted transaction line becomes a row on your spreadsheet under the account label you choose. The date, description and amount are copied exactly as printed, currency symbols, minus signs and brackets included, and saved as plain text so nothing becomes a formula or another date. When the statement prints money in and money out in separate columns, an In/Out column says which one each amount came from.

Nothing is calculated: balances and subtotals are left out. Where part of a line was not printed, the cell is left blank and the notes column says so, and a line that cannot be split into date, description and amount is not written at all. Running the same statement again never writes a line twice: lines already on the tab for that account are skipped. If any line is skipped, fails to write or falls past the row limit, a clearly marked 'INCOMPLETE' row is added with the counts, so a partial run never looks like a complete one.

You need an Extend connection (it reads the PDF) and a Google Sheets connection, the statement PDF, the link to your spreadsheet, the name of the tab, and a label for the account.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set tabName to blank.Set tabName to blank.

Set accountLabel to blank.Set accountLabel to blank.

Set maxRows to 200.Set maxRows to 200.

Round maxRows multiplied by 1 to the nearest integer and save it as rowLimit.Round maxRows multiplied by 1 to the nearest integer and save it as rowLimit.

Set writtenLines to an empty list.Set writtenLines to an empty list.

If a step below fails, save the error as tabReadErrorIf a step below fails, save the error as tabReadError

Try

Google SheetsRead the rows of spreadsheetUrl. Save it as tabRows.Read the rows of spreadsheetUrl. Save it as tabRows.

If tabRows.rows is emptyIf tabRows.rows is empty

Yes

Google SheetsAdd a row to tabName in spreadsheetUrl.Add a row to tabName in spreadsheetUrl.

No

Build the text @tabRows.rows[0].column_1, @tabRows.rows[0].column_2, @tabRows.rows[0].column_3, @tabRows.rows[0].column_4, @tabRows.rows[0].column_5, @tabRows.rows[0].column_... and save it as headingText.Build the text @tabRows.rows[0].column_1, @tabRows.rows[0].column_2, @tabRows.rows[0].column_3, @tabRows.rows[0].column_4, @tabRows.rows[0].column_5, @tabRows.rows[0].column_... and save it as headingText.

If headingText in lowercase is not "recorded on, account, date, description,…If headingText in lowercase is not "recorded on, account, date, description,…

Yes

Replace the pattern (,\s*)+$ with in headingText and save it as headingsFound.Replace the pattern (,\s*)+$ with in headingText and save it as headingsFound.

Stop and report a problem: The first row of the '@tabName' tab must hold these headings, in this order: Recorded on, Account, Date, Description, Amount, In/Out, Notes. It now reads: headingsFound. Rows are written by position, so fix the headings (or point 'tabName' at a new, empty tab) and run it again.Stop and report a problem: The first row of the '@tabName' tab must hold these headings, in this order: Recorded on, Account, Date, Description, Amount, In/Out, Notes. It now reads: headingsFound. Rows are written by position, so fix the headings (or point 'tabName' at a new, empty tab) and run it again.

No

No steps

Keep each item in tabRows.rows where item.column_2 is accountLabel and save it as accountRows.Keep each item in tabRows.rows where item.column_2 is accountLabel and save it as accountRows.

Keep only column_3, column_4, column_5, and column_6 in accountRows and save it as writtenLines.Keep only column_3, column_4, column_5, and column_6 in accountRows and save it as writtenLines.

On error

Stop and report a problem: Could not read the '@tabName' tab, so nothing was written (without it, lines from an earlier run could be written twice): tabReadError.message Check the link in 'spreadsheetUrl', that the tab exists with exactly that name, and that the Google account connected to Lodol can edit the sheet.Stop and report a problem: Could not read the '@tabName' tab, so nothing was written (without it, lines from an earlier run could be written twice): tabReadError.message Check the link in 'spreadsheetUrl', that the tab exists with exactly that name, and that the Google account connected to Lodol can edit the sheet.

Set statementLines to an empty list.Set statementLines to an empty list.

If a step below fails, save the error as statementErrorIf a step below fails, save the error as statementError

Try

ExtendExtract data from a document. Save it as statement.Extract data from a document. Save it as statement. Fields to extract: transaction_linesCopy what is printed on the page and nothing else. Do not calculate, convert, round, re-sign, complete or reformat any date, description or amount: reproduce the characters printed on the statement, including any currency symbol, minus sign or brackets. If part of a line is not ...

Use statement.data.transaction_lines, or [] if it is empty and save it as statementLines.Use statement.data.transaction_lines, or [] if it is empty and save it as statementLines.

On error

Stop and report a problem: Could not read the statement (statementError.message). Upload the statement PDF in the File field of the 'Extract data from a document' step (or paste a link there that opens the PDF without signing in), make sure Extend is connected, then run it again. Nothing was written.Stop and report a problem: Could not read the statement (statementError.message). Upload the statement PDF in the File field of the 'Extract data from a document' step (or paste a link there that opens the PDF without signing in), make sure Extend is connected, then run it again. Nothing was written.

Set linesFound to 0.Set linesFound to 0.

Set rowsWritten to 0.Set rowsWritten to 0.

Set linesUnreadable to 0.Set linesUnreadable to 0.

Set linesFailed to 0.Set linesFailed to 0.

Set linesAlreadyWritten to 0.Set linesAlreadyWritten to 0.

For each line in statementLines:For each line in statementLines:

Add 1 to linesFound.Add 1 to linesFound.

Trim line and save it as lineText.Trim line and save it as lineText.

If lineText is null or lineText is emptyIf lineText is null or lineText is empty

Yes

Add 1 to linesUnreadable.Add 1 to linesUnreadable.

Skip to the next item.Skip to the next item.

No

No steps

Split lineText by || and save it as parts.Split lineText by || and save it as parts.

If the length of parts is less than 3 or the length of parts is greater than 4If the length of parts is less than 3 or the length of parts is greater than 4

Yes

Add 1 to linesUnreadable.Add 1 to linesUnreadable.

Skip to the next item.Skip to the next item.

No

No steps

Trim parts[0] and save it as dateCell.Trim parts[0] and save it as dateCell.

Trim parts[1] and save it as descriptionCell.Trim parts[1] and save it as descriptionCell.

Trim parts[2] and save it as amountCell.Trim parts[2] and save it as amountCell.

Trim parts[3] and save it as directionText.Trim parts[3] and save it as directionText.

Look up directionText in a table and save it as directionCell.Look up directionText in a table and save it as directionCell.

If dateCell is empty and descriptionCell is empty and amountCell is emptyIf dateCell is empty and descriptionCell is empty and amountCell is empty

Yes

Add 1 to linesUnreadable.Add 1 to linesUnreadable.

Skip to the next item.Skip to the next item.

No

No steps

Find the position of a record of column 3, column 4, column 5, and column 6 in writtenLines and save it as earlierCopy.Find the position of a record of column 3, column 4, column 5, and column 6 in writtenLines and save it as earlierCopy.

If earlierCopy is greater than or equal to 0If earlierCopy is greater than or equal to 0

Yes

Remove the item at position earlierCopy from writtenLines and save it as writtenLines.Remove the item at position earlierCopy from writtenLines and save it as writtenLines.

Add 1 to linesAlreadyWritten.Add 1 to linesAlreadyWritten.

Skip to the next item.Skip to the next item.

No

No steps

If rowsWritten is greater than or equal to rowLimitIf rowsWritten is greater than or equal to rowLimit

Yes

Skip to the next item.Skip to the next item.

No

No steps

Set rowNote to blank.Set rowNote to blank.

If dateCell is empty or descriptionCell is empty or amountCell is emptyIf dateCell is empty or descriptionCell is empty or amountCell is empty

Yes

Change rowNote to Blank cell - that part was not printed on the statement. Check this line before you use it..Change rowNote to Blank cell - that part was not printed on the statement. Check this line before you use it..

No

No steps

If a step below fails, save the error as writeErrorIf a step below fails, save the error as writeError

Try

Google SheetsAdd a row to tabName in spreadsheetUrl.Add a row to tabName in spreadsheetUrl.

Add 1 to rowsWritten.Add 1 to rowsWritten.

On error

Add 1 to linesFailed.Add 1 to linesFailed.

Show in the output: A transaction line could not be written to the spreadsheet: writeError as text.Show in the output: A transaction line could not be written to the spreadsheet: writeError as text.

If linesFound is 0If linesFound is 0

Yes

Stop and report a problem: No transaction lines were read from that file, so nothing was written. Check the file is the bank statement itself rather than a scan of a summary page, and that it opens as a PDF.Stop and report a problem: No transaction lines were read from that file, so nothing was written. Check the file is the bank statement itself rather than a scan of a summary page, and that it opens as a PDF.

No

No steps

Set linesHandled to rowsWritten plus linesAlreadyWritten.Set linesHandled to rowsWritten plus linesAlreadyWritten.

Set linesDropped to linesFound minus linesHandled.Set linesDropped to linesFound minus linesHandled.

Build the text @linesFound transaction lines were read from the statement: @rowsWritten written now, @linesAlreadyWritten already on the tab from an earlier run, @linesUnread... and save it as runSummary.Build the text @linesFound transaction lines were read from the statement: @rowsWritten written now, @linesAlreadyWritten already on the tab from an earlier run, @linesUnread... and save it as runSummary.

If linesDropped is greater than 0If linesDropped is greater than 0

Yes

If a step below fails, save the error as noteErrorIf a step below fails, save the error as noteError

Try

Google SheetsAdd a row to tabName in spreadsheetUrl.Add a row to tabName in spreadsheetUrl.

On error

Show in the output: The incomplete-statement note could not be written to the spreadsheet: noteError as text.Show in the output: The incomplete-statement note could not be written to the spreadsheet: noteError as text.

If linesUnreadable plus linesFailed is greater than 0If linesUnreadable plus linesFailed is greater than 0

Yes

Stop and report a problem: runSummary Lines that could not be read are not on the tab: check them against the statement. Running it again retries any line that failed to write or fell past the limit; lines already on the tab are skipped.Stop and report a problem: runSummary Lines that could not be read are not on the tab: check them against the statement. Running it again retries any line that failed to write or fell past the limit; lines already on the tab are skipped.

No

Finish the run: runSummary Writing stopped at the 'maxRows' limit. Run it again to write the rest: lines already on the tab are skipped, so nothing is written twice.Finish the run: runSummary Writing stopped at the 'maxRows' limit. Run it again to write the rest: lines already on the tab are skipped, so nothing is written twice.

No

No steps

If rowsWritten is 0If rowsWritten is 0

Yes

Finish the run: All linesFound transaction lines on this statement are already on the '@tabName' tab for accountLabel, so nothing new was written. To add another statement, upload it in the File field of the 'Extract data from a document' step.Finish the run: All linesFound transaction lines on this statement are already on the '@tabName' tab for accountLabel, so nothing new was written. To add another statement, upload it in the File field of the 'Extract data from a document' step.

No

No steps

If linesAlreadyWritten is greater than 0If linesAlreadyWritten is greater than 0

Yes

Finish the run: runSummary Every line of this statement is now on the tab, so you can delete any [INCOMPLETE] row an earlier run left for it.Finish the run: runSummary Every line of this statement is now on the tab, so you can delete any [INCOMPLETE] row an earlier run left for it.

No

No steps

Finish the run: runSummary.Finish the run: runSummary.