Reads every transaction line off a bank statement PDF and adds one row per line to your spreadsheet, exactly as printed.
Uses
Extend
Google Sheets
Turn a bank statement into transaction rows
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 tabReadErrorTry
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 emptyYes
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 headingText and save it as headingsFound.Replace the pattern (,\s*)+$ with 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 statementErrorTry
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 emptyYes
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 4Yes
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 emptyYes
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 0Yes
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 rowLimitYes
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 emptyYes
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 writeErrorTry
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 0Yes
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 0Yes
If a step below fails, save the error as noteErrorIf a step below fails, save the error as noteErrorTry
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 0Yes
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 0Yes
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 0Yes
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.