Classification of bank transactions in Excel
I have an exported list of bank transactions in Excel that I would like to categorize as quickly and easily as possible. I suspect this is only possible with array formulas, but the VBA function would be equally useful.
Scenario
My table header looks like this:
| A | B | C | D |
==========================================
| Date | Description | Amount | Category |
------------------------------------------
Date, description and amount are filled in from my bank. I fill in the category for each transaction in column D.
This is fine, but time-consuming because each category must be entered individually and manually.
Necessity
I want the categories to be auto-populated on rows where I did not enter the category manually, based on rules that are dynamically generated and applied.
The result I want is as follows:
| A | B | C | D | E | F |
==============================================================================
| Date | Description | Amount | Manual cat. | Rule | Auto cat. |
------------------------------------------------------------------------------
| 04/08/12 | Starbucks NYC | -$5.42 | Coffee | starbucks | Coffee |
| 04/09/12 | Wal-Mart 468 | -$54.32 | Supermarket | wal-mart | Supermarket |
| 04/10/12 | Starbucks SF | -$3.68 | | | Starbucks |
As you can see, I entered "manual" categories in column D. Wherever I did this, I entered a categorization rule in column E. Then Excel uses my records to automatically populate column F.
The logic is simple:
-
When I enter a category manually, Excel does two things:
- Configures my guide category in column F.
- Create a rule using the text entered in column E.
-
Where Excel meets a description that contains text used in one of my rules, it fills in the corresponding category in column F.
Benefits
It would make it very easy to list the list of transactions by adding categories and related rules. Transactions without any category will be auto-filled when the rule is applied and will remain empty if the rule is not applied. Transactions in which a rule was applied giving the wrong category can be corrected and a new rule added.
My best try so far
I created a way to do this using only formulas, but has three drawbacks:
- This requires creating as many columns as possible.
- Unable to specify categories and associated rules.
- The order in which the rules are applied cannot be changed.
source to share
Introduction
As I pointed out earlier, the solution below is over-engineered for your immediate needs as
it is for my needs, which are more varied than the ones you list in your question:
- I am in the business of changing banks, so I have two checking (checking) accounts and two credit card accounts. I also have several savings accounts. I keep e-statements for all of these accounts, which are in different formats, in several books.
- Your example example is very neat compared to the ones I get. Here are some descriptions from a recent MasterCard statement in which I tidy up my preferred "Organization Name, Location" format.
...
SAINSBURY S/MKT MONKS CROSS
Amazon *Mktplce EU-UK AMAZON.CO.UK LUX
WRAP LOUGHBOROUGH
SAINSBURYS PETROL MONKS CROSS
- Like you, I categorize transactions.
- Some organizations bill annually or quarterly in advance or in arrears. My income varies from month to month. For these transactions, I distribute the amount over the respective months so that the result better reflects my true financial situation.
My solution to these multiple requirements is to have a management routine for each account that knows where it is and what each column is used for. They are called common routines that take a workbook, worksheet, etc. as parameters and perform the necessary transformations and additions. At the heart of these transformations and additions is a worksheet that I called "Rules", which has three columns:
RuleType A code such as "OrgCat"
In-keyword A string, such as "Starbucks", to be found in a text column
Out-keyword A string, such as "Coffee", to be returned if the In-keyword
is found
Other types of rules that I use include:
"OrgOrg" Convert an organisation name used in the source statement to my
preferred name for the organisation.
"CatPer" Return a code identifying the apportioning rule for a category. For
example, "Utility" returns "B3" (Back 3) because my utility bills
are issued for three months in arrears.
In your question, you have a "scenerio version" of your account and a "need version" of your account. I am assuming that you have manually created the "correct version" of your account so you can see what it looks like. I've provided the CopyFromAcctToRule () macro that works with the "correct version" of your account, checks and retrieves rules like "OrgCat". If it finds no errors, it outputs the extracted rules to the Rule worksheet and converts the "desired version" to the "scenerio version". If you haven't created the "desired version", I suspect the simplest approach is to create a partial "desired version" like this:
| A | B | C | D | E |
================================================================
| Date | Description | Amount | Category | Rule |
| 04/08/12 | Starbucks NYC | -$5.42 | Coffee | Starbucks |
| 04/09/12 | Wal-Mart 468 | -$54.32 | Supermarket | Wal-Mart |
| 04/10/12 | Starbucks SF | -$3.68 | | |
| 04/11/12 | Wal-Mart 512 |-$123.45 | | |
That is, find the first Starbucks and fill in its category and rule; Find the first Wal-Mart and complete its category and rule; etc. Run CopyFromAcctToRule () and it will display error messages in column "G" for inconsistencies and organizations you missed. For one-off fillings, fill in the Category, but leave the rule blank. Repeat, fix errors, and run CopyFromAcctToRule () until you find errors and create a Rule sheet. Note: missing categories will not be added at this stage; what happens below.
I have provided a FillDerivedCol () macro showing how I use it by populating the Category column in the "scenerio version" account. If you don't want to create a partial "desired version", FillDerivedCol () offers an alternative approach. If he cannot find a category for the description, he copies the description at the bottom of the Rule sheet. For example, suppose you have turned down a rule against Starbucks, the "Rule" will be changed to:
| A | B | C |
===========================================
| Type | In keyword | Out keyword |
| OrgCat | Sarbucks | Coffee |
| OrgCat | Wal-Mart | Supermarket |
| OrgCat | Starbucks NYC | |
| OrgCat | Starbucks SF | |
That is, there will be one new line for each Starbucks branch. Here the easiest way is to fix Sarbucks line and remove new lines. However, if this was a new organization, you can edit the In keyword to remove the branch information and enter a category in the Out-keyword column. Warning: I've exceeded 30,000 characters for the answer. I had to edit these routines to remove the diagnostic code. Hope I have not presented any errors with this.
Hope this is helpful. Good luck.
Global
These global constants and subroutine are used by both macros mentioned above. I put them in my own module, but that's your choice.
Option Explicit
' I use constant for objects such as column numbers which are fixed
' for long periods but which might change. Any code using a column
' that has moved can be updated by changing the constant.
Public Const ColRuleType As Long = 1
Public Const ColRuleKeywordIn As Long = 2
Public Const ColRuleKeywordOut As Long = 3
Public Const ColRuleLast As Long = 3
Public Const RowRuleDataFirst As Long = 2
' Rules are accumulated in this array by CopyFromAcctToRule
' Rules are loaded to this array by UpdateNewTransactions
' See GetRuleDetails() for a description of this array.
Public RuleData() As Variant
Public Sub GetRuleDetails(ByVal RuleType As String, ByVal SrcText As String, _
ByRef KeywordIn As String, ByRef KeywordOut As String, _
Optional ByRef RowRuleSrc As Long)
' This routine performs a case-insensive search of a list of in-keywords for
' one that is present in SrcText. If one is found, it returns the in-keyword
' and the matching out-keyword.
' This routine uses the previously prepared array RuleData. Since RuleData
' is to be loaded to, or has been loaded from, a worksheet, the first
' dimension is for the rows and the second dimension is for the columns.
' RuleData has three columns:
' * RuleType: a code identifying a type of rule. Only rows in RuleData for
' which this column matches the parameter RuleType will be considered.
' * KeywordIn: a string. The first row in RuleData where the value of this
' column is contained within parameter SrcText is the selected Rule.
' * KeywordOut: a string.
' Input parameters
' * RuleType: Foe example, the rule type "OrgCat" will return a
' category for an organisation.
' * SrcText: The text field to be searched for the in keyword.
' Output parameters
' * KeywordIn: The value from the KeywordIn column of RuleData for the first
' row of RuleData of the required RuleType for which the KeywordIn value can
' be found in Desc. The value in SrcText may be of any case although it is
' likely to be capitalised. This value is the preferred display value.
' * KeywordOut: The value from the KeywordOut column of RuleData of the
' selected row. For this routine, KeywordOut is a string with no
' significance. It is the calling routine that understands the rule type.
' * RowRuleSrc: Only used during build of RuleData so the caller can access
' non-standard data held in RuleData during build.
Dim LCSrcText As String
Dim RowRuleCrnt As Long
LCSrcText = LCase(SrcText)
For RowRuleCrnt = RowRuleDataFirst To UBound(RuleData, 1)
If RuleData(RowRuleCrnt, ColRuleKeywordIn) = "" Then
' Empty row. This indicated end of table during build
KeywordIn = ""
KeywordOut = ""
Exit Sub
End If
If RuleType = RuleData(RowRuleCrnt, ColRuleType) Then
' This row is for the required type of rule
If InStr(1, LCSrcText, _
LCase(RuleData(RowRuleCrnt, ColRuleKeywordIn))) <> 0 Then
' Have found first rule with KeywordIn contained within SrcText
KeywordIn = RuleData(RowRuleCrnt, ColRuleKeywordIn)
KeywordOut = RuleData(RowRuleCrnt, ColRuleKeywordOut)
If Not IsEmpty(RowRuleSrc) Then
RowRuleSrc = RowRuleCrnt
End If
Exit Sub
End If
End If
Next
' No rule found
KeywordIn = ""
KeywordOut = ""
End Sub
Extract rules and convert account from Need to Scenerio style
See the Introduction for how I will use this procedure. Once you've created a Rules worksheet for existing transactions, this code probably won't make any difference. I would place it in my own module so that it can be archived and removed after use. This code assumes that the Rule and Matt Acct worksheets are in the same workbook. I suggest you make a copy of your account, create a Rule worksheet, and then run CallCopyFromAcctRule () on the copy account and evaluate the result. Warning: you are using "rule" where I am using "keyword"; I've tried to be consistent in my comments and bug reports, but can't guarantee what I have.
Option Explicit
Sub CallCopyFromAcctRule()
' This routine exists simply to make it easy to change the names of the
' worksheets accessed by CallCopyFromAcctRule.
Call CopyFromAcctToRule("Rule", "Matt Acct")
End Sub
Sub CopyFromAcctToRule(ByVal Rule As String, ByVal Acct As String)
' * This routine builds the worksheet Rule from worksheet Acct.
' * It works down worksheet Acct extracting rules from rows where
' there is both a Rule and a Category. Note: this routine does not
' distinguish between Manual and Automatic Categories although, if both are
' present, they must be the same.
' * The routine checks for a variety of error and possible error conditions.
' Error and warning messages are placed in columns defined by ColAcctError
' and ColAcctWarn.
' * If any errors are found, the routine does not change either worksheet
' Acct, apart from adding error messages, or worksheet Rule.
' * If no errors are found, worksheet Rule is cleared and the contents of
' RuleData written to it.
' * If no errors are found, any warning added to worksheet Acct are discarded
' and the following additional changes made:
' * The values in the Automatic category column are merged into the Manual
' category column which is relabelled "Category".
' * The Rule and Automatic category columns are cleared.
Dim ColAcctCatAuto As Long
Dim ColAcctCatMan As Long
Dim ColAcctCrnt As Long
Dim ColAcctDesc As Long
Dim ColAcctError As Long
Dim ColAcctRule As Long
Dim ColAcctWarn As Long
Dim ColRuleRowSrc As Long
Dim DescCrnt As String
Dim ErrorFoundAll As Boolean
Dim ErrorFoundCrnt As Boolean
Dim KeywordInCrnt As String
Dim KeywordInRetn As String
Dim KeywordOutCrnt As String
Dim KeywordOutRetn As String
Dim RowAcctCrnt As Long
Dim RowAcctDataFirst As Long
Dim RowAcctLast As Long
Dim RowRuleCrntMax As Long
Dim RowRuleSrc As Long
' These column values must be changed if the true value do not match those
' in the example in the question.
ColAcctDesc = 2
ColAcctCatMan = 4
ColAcctRule = 5
ColAcctCatAuto = 6
ColAcctError = 8
ColAcctWarn = 9
ColRuleRowSrc = ColRuleLast + 1
RowAcctDataFirst = 2
With Worksheets(Acct)
RowAcctLast = .Cells.SpecialCells(xlCellTypeLastCell).Row
' Size the array for the output data ready to be loaded to worksheet
' Rule with rows as the first dimension. Allow for the maximum number of
' rows because an array cannot be resized to change the number of
' elements in the first dimension. Allow an extra column for use during
' the build process.
ReDim RuleData(1 To RowAcctLast, 1 To ColRuleRowSrc)
RuleData(1, ColRuleType) = "Type"
RuleData(1, ColRuleKeywordIn) = "In keyword"
RuleData(1, ColRuleKeywordOut) = "Out keyword"
RowRuleCrntMax = 1 ' Last currently used row
With .Cells(1, ColAcctError)
.Value = "Error"
.Font.Bold = True
End With
With .Cells(1, ColAcctWarn)
.Value = "Warning"
.Font.Bold = True
End With
ErrorFoundAll = False
For RowAcctCrnt = RowAcctDataFirst To RowAcctLast
.Cells(RowAcctCrnt, ColAcctError).Value = "" ' Clear any error or warning
.Cells(RowAcctCrnt, ColAcctWarn).Value = "" ' from previous run
ErrorFoundCrnt = False
' Determine Category, if any
If .Cells(RowAcctCrnt, ColAcctCatMan).Value = "" Then
' There is no manual category.
If .Cells(RowAcctCrnt, ColAcctCatAuto).Value <> "" Then
KeywordOutCrnt = .Cells(RowAcctCrnt, ColAcctCatAuto).Value
Else
' Neither manual nor automatic category
KeywordOutCrnt = ""
End If
Else
' There is a manual category. Is it consistent with automatic category?
KeywordOutCrnt = .Cells(RowAcctCrnt, ColAcctCatMan).Value
If .Cells(RowAcctCrnt, ColAcctCatAuto).Value <> "" Then
' Automatic category exists. It must be the same
' as the manual category to be valid.
If LCase(KeywordOutCrnt) <> _
LCase(.Cells(RowAcctCrnt, ColAcctCatAuto).Value) Then
ErrorFoundCrnt = True
.Cells(RowAcctCrnt, ColAcctError).Value = _
"Manual and automatic categories different"
End If
End If
End If
If Not ErrorFoundCrnt Then
' Match Rule, if any, against Category, if any
KeywordInCrnt = .Cells(RowAcctCrnt, ColAcctRule).Value
If KeywordInCrnt <> "" Then
' This row has keyword
If KeywordOutCrnt = "" Then
' Rule but no Category
DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, KeywordOutRetn)
If KeywordInRetn <> "" Then
' Rule found that would generate a category for this Keyword.
' No warning necessary
Else
' No rule found that would generate a category for this keyword
ErrorFoundCrnt = True
.Cells(RowAcctCrnt, ColAcctError).Value = _
"There is no existing rule that would " & _
"generate a Category from this Rule"
End If
Else
' Both Rule and Category found
' Is match already recorded?
DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, _
KeywordOutRetn, RowRuleSrc)
If KeywordInRetn <> "" Then
If KeywordInCrnt <> KeywordInRetn Then
' A different rule would be applied to this Description
If InStr(1, LCase(DescCrnt), LCase(KeywordInCrnt)) = 0 Then
' The current Rule is not within the Description
ErrorFoundCrnt = True
.Cells(RowAcctCrnt, ColAcctError).Value = _
"The Rule in column " & Chr(64 + ColAcctRule) & _
" is not within the Description. The Rule " & _
"from row " & RowRuleSrc & " would generate " & _
"the required Category '" & KeywordOutRetn & _
"' from this Description"
Else
' The current Rule is within the Description
If LCase(KeywordOutRetn) = LCase(KeywordOutCrnt) Then
' It would generate the same category
ErrorFoundCrnt = True
.Cells(RowAcctCrnt, ColAcctError).Value = _
"The Rule in column " & Chr(64 + ColAcctRule) & _
" is within the Description but the Rule from " & _
"row " & RowRuleSrc & " would be selected to " & _
"generate the required Category '" & _
KeywordOutRetn & "' from this Description"
Else
' It would generate a different category
ErrorFoundCrnt = True
.Cells(RowAcctCrnt, ColAcctError).Value = _
"The Rule in column " & Chr(64 + ColAcctRule) & _
" is within the Description but the Rule from " & _
"row " & RowRuleSrc & " would be selected to " & _
"generate Category '" & KeywordOutRetn & _
"', not Category '" & KeywordOutCrnt & _
"', from this " & "Description"
End If
End If
Else
' Rule already recorded
If LCase(KeywordOutRetn) = LCase(KeywordOutCrnt) Then
' Rule already recorded for this category. No action required.
Else
' Rule already recorded but not for this category
ErrorFoundCrnt = True
.Cells(RowAcctCrnt, ColAcctError).Value = _
"The rule from row " & RowRuleSrc & _
" would generate category """ & _
KeywordOutRetn & """ for this Rule"
End If
End If
Else
' New rule
RowRuleCrntMax = RowRuleCrntMax + 1
RuleData(RowRuleCrntMax, ColRuleType) = "OrgCat"
RuleData(RowRuleCrntMax, ColRuleKeywordOut) = KeywordOutCrnt
RuleData(RowRuleCrntMax, ColRuleKeywordIn) = KeywordInCrnt
RuleData(RowRuleCrntMax, ColRuleRowSrc) = RowAcctCrnt
End If
End If ' If CatCrnt = ""
Else
' No keyword
If KeywordOutCrnt = "" Then
' No Keyword and no Category
DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
If DescCrnt = "" Then
' Probably a blank line. Ignore
Else
' Would an existing rule generate a Category for Description
Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, KeywordOutRetn)
If KeywordInRetn = "" Then
' No rule found that would generate a category
' for this description
.Cells(RowAcctCrnt, ColAcctError).Value = _
"There is no rule that would generate " & _
"a Category from this Description"
Else
' Rule found that would generate a category for
' this description.
End If
End If
Else
' No Keyword but have Category
' Check for a rule that would give current category
' from current description
DescCrnt = .Cells(RowAcctCrnt, ColAcctDesc).Value
Call GetRuleDetails("OrgCat", DescCrnt, KeywordInRetn, _
KeywordOutRetn, RowRuleSrc)
If KeywordInRetn <> "" Then
' Have found a rule for the description
If LCase(KeywordOutRetn) = LCase(KeywordOutCrnt) Then
' Rule generates current category
Else
' Rule does not generate current category
ErrorFoundCrnt = True
.Cells(RowAcctCrnt, ColAcctError).Value = _
"The rule from row " & RuleData(RowRuleSrc, ColRuleRowSrc) & _
" would generate Category '" & KeywordOutRetn & _
"' from this Description"
End If
Else
' There is no rule for this Description. This is not necessarily
' an error. The category may have to be set manually.
.Cells(RowAcctCrnt, ColAcctWarn).Value = _
"There is no rule that would generate " & _
"this Category from this Description"
End If
End If ' If KeywordOutCrnt = ""
End If ' KeywordInCrnt <> ""
End If ' If Not ErrorFoundCrnt
If ErrorFoundCrnt Then
ErrorFoundAll = True
End If
Next
End With
If ErrorFoundAll Then
Exit Sub
End If
' No errors found
' Clear existing contents from worksheet Rule and load with RuleData
With Worksheets(Rule)
.Cells.EntireRow.Delete
.Range(.Cells(1, 1), .Cells(RowRuleCrntMax, _
ColRuleKeywordOut)).Value = RuleData
.Range("A1:C1").Font.Bold = True
.Columns.AutoFit
End With
With Worksheets(Acct)
' Merge values from automatic category column into manual category column
For RowAcctCrnt = 2 To RowAcctLast
If .Cells(RowAcctCrnt, ColAcctCatMan).Value = "" Then
' There is no manual category so set to automatic category.
.Cells(RowAcctCrnt, ColAcctCatMan).Value = _
.Cells(RowAcctCrnt, ColAcctCatAuto).Value
End If
Next
' Clear automatic category
.Columns(ColAcctCatAuto).ClearContents
' Change column heading
With .Cells(1, ColAcctCatMan)
.Value = "Category"
.Font.Bold = True
End With
' Clear Error and Warning columns
.Columns(ColAcctError).ClearContents ' Only heading to clear
.Columns(ColAcctWarn).ClearContents
' Clear Rule column
.Columns(ColAcctRule).ClearContents
End With
End Sub
Completing the Category column of your scenerio version account
This demonstrates how I populate the Category column for new transactions.
Option Explicit
Sub CallFillDerivedCol()
' I use FillDerivedCol() on worksheets loaded with transactions for different
' accounts. They are in different workbooks, different worksheets and have
' different columns. This routine exists to call FillDerivedCol() for my
' test version of your account
Call FillDerivedCol(ActiveWorkbook, "Rule", _
ActiveWorkbook, "Matt Acct", "OrgCat", 2, 4)
' For this example, I had the rules and the account in same workbook. To
' have them in different workbooks, as I normally do, you will need something
' like:
' Dim PathCrnt As String
' Dim WBookOrig As Workbook
' Dim WBookOther As Workbook
' Set WBookOrig = ActiveWorkbook
' PathCrnt = ActiveWorkbook.Path & "\"
' Set WBookOther = Workbooks.Open(PathCrnt & "xxxxxxx")
' Call FillDerivedCol(WBookOrig, "Rule", _
' WBookOther, "Matt Acct", "OrgCat", 2, 4)
' WBookOther.Close SaveChanges:=True
End Sub
Sub FillDerivedCol(ByVal WBookRule As Workbook, ByVal WSheetRule As String, _
ByVal WBookTrans As Workbook, ByVal WSheetTrans As String, _
ByVal RuleType As String, _
ByVal ColSrc As Long, ByVal ColDest As Long)
' Fill any gaps in WBookTrans.Worksheets(WSheetTrans).Columns(ColDest) based on
' rules in worksheet WBookRule.Worksheets(WSheetRule).
' WBook.Worksheets(WSheetTrans).Columns(ColSrc) is a text field which
' contains in-keywords. Rules of type RuleType convert in-keywords to
' out-keywords which are the values required for .Columns(ColDest).
Dim CellEmptyDest As Range
Dim KeywordIn As String
Dim KeywordOut As String
Dim MissingRule() As Variant
Dim RowAcctCrnt As Long
Dim RowAcctPrev As Long
Dim RowMissingCrntMax As Long
Dim RowRuleLast As Long
' Load array RuleData from worksheet Rule
With WBookRule.Worksheets(WSheetRule)
RowRuleLast = .Cells(Rows.Count, 1).End(xlUp).Row
RuleData = .Range(.Cells(1, 1), .Cells(RowRuleLast, ColRuleLast)).Value
End With
' * Prepare MissingRule() in case any calls to GetRuleDetails() fails to
' find a known in-keyword in WBook.Worksheets(WSheetName).Columns(ColDest).
' * The number of occurrences of the first dimension cannot be changed. 500
' is intended to be more occurrences than could possible be needed. If
' more than 500 missing rules are found, only the first 500 will be added
' to worksheet "Rule" This routine can be immediately run again to add
' another 500 missing rules.
ReDim MissingRule(1 To 500, 1 To ColRuleLast)
RowMissingCrntMax = 0
With WBookTrans
With .Worksheets(WSheetTrans)
RowAcctPrev = 1
' Find the next empty cell in column ColDest for a transaction row
Set CellEmptyDest = .Columns(ColDest).Find(What:="", _
After:=.Cells(RowAcctPrev, ColDest), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
Do While True
If CellEmptyDest Is Nothing Then
' No empty cell found in column. This is not a realistic situation
' because it would require every row in the worksheet to have a value.
Exit Do
End If
RowAcctCrnt = CellEmptyDest.Row
If RowAcctCrnt < RowAcctPrev Then
' Have looped back to the top. This is not a realistic situation
' because it would require every row in the worksheet to have a value.
Exit Do
End If
If .Cells(RowAcctCrnt, ColSrc).Value = "" Then
' This row has no value in either the source or the destination
' columns. Assume all transactions finished
Exit Do
End If
Call GetRuleDetails(RuleType, .Cells(RowAcctCrnt, ColSrc).Value, _
KeywordIn, KeywordOut)
If KeywordIn = "" Then
' No in-keyword found within source column. Add source column value
' to MissingData for user to edit.
If RowMissingCrntMax >= UBound(MissingRule, 1) Then
' All available rows in MissingRule already used
Else
RowMissingCrntMax = RowMissingCrntMax + 1
MissingRule(RowMissingCrntMax, ColRuleType) = RuleType
MissingRule(RowMissingCrntMax, ColRuleKeywordIn) = _
.Cells(RowAcctCrnt, ColSrc).Value
End If
Else
.Cells(RowAcctCrnt, ColDest).Value = KeywordOut
End If
RowAcctPrev = RowAcctCrnt
Set CellEmptyDest = .Columns(ColDest).FindNext(CellEmptyDest)
Loop
End With
End With
If RowMissingCrntMax > 0 Then
' Transactions found for which no rule exists. Add to worksheet "Rule"
' for attention by the user.
With WBookRule.Worksheets(WSheetRule)
RowRuleLast = .Cells(Rows.Count, 1).End(xlUp).Row
.Range(.Cells(RowRuleLast + 1, 1), _
.Cells(RowRuleLast + RowMissingCrntMax, ColRuleLast)).Value _
= MissingRule
End With
End If
End Sub
source to share
I do something similar with my credit card statements. I use VBA because I believe that descriptions are incompatible and require different methods to classify them.
The approach I am using is to have a worksheet, which I called Rule, containing:
Organisation Category
Starbucks NYC Coffee shop
Starbucks SF Coffee shop
Wal-Mart 468 Supermarket
Notice I have a line for each branch. It's a pain if you travel a lot, but without consistency, choices are few.
In column D of the operator I enter =VLOOKUP(B2,Rule!A:B,2,FALSE)
and then copy it.
Every month new organizations are classified as "# N / A". I either print the one-off classification or add the organization to the sheet rule.
source to share
This seems like a dead thread, but I ran into the same problem when my bank asked me for details on my monthly expenses.
I didn't want to write VBA, so I wrote a PowerShell script to do it for me. it has an array called $Rules
where you define templates and their category. The last template matches the category for the item. I add * to the end of each pattern and use the -like operator.
This is a bit slower because PowerShell is slow to access Excel cells and takes a couple of minutes for the 1000 lines I have on my bank statement.
$DesColumn
refers to the description of the bank account description, and $CatColumn
is the column in which the category will be saved.
After applying the script, you can use Excel's PIVOT functions to create pie charts that summarize your data. Don't forget to back up your file!
$xl = New-Object -comobject Excel.Application
# Show Excel
$xl.visible = $false
$xl.DisplayAlerts = $False
# Create a workbook
$wb = $xl.Workbooks. open("C:\Accounting\Accounting_2013.xls" )
# Get sheets
$ws = $wb.WorkSheets.item( "Costs")
$ws.activate()
$DescColumn = 6
$CatColumn = 7
$Rng = $ws.UsedRange.Cells
$intRowMax = $Rng.Rows.Count
#$intRowMax = 50
$Rules =@(
@("*FOOD","GROCERY"),
@("*Hotel","FUN"),
@("*ADVENTURES","FUN"),
@("CINEPLEX","FUN"),
@("EVENT CINEMAS","FUN"),
@("*Rent","RENT"),
@("Wdl ATM","ATM"),
@("IKEA","HOME"),
@("FORM HOME","HOME"),
@("KMART","HOME"),
@("BIG W","HOME"),
@("PILLOW TALK","HOME"),
@("BUNNING","HOME")
@("IGA","GROCERY"),
@("COLES","GROCERY"),
@("ALDI","GROCERY"),
@("FRUITY CAPERS","GROCERY"),
@("WOOLWORTHS","GROCERY"),
@("MEGAFRESH","GROCERY"),
@("CALTEX","CAR"),
@("COLES EXP","CAR"),
@("CTX WOW","CAR"),
@("BP EXPRESS","CAR"),
@("QLD TRANSPORT","CAR"),
@("REPCO","CAR"),
@("FREEDOM FUEL","CAR"),
@("BP THE GAP","CAR"),
@("MCDONALDS","DINE"),
@("RED ROOSTER","DINE"),
@("*SIZZLER","DINE"),
@("DOMINO","DINE"),
@("SUBWAY","DINE"),
@("ROUTE 74","DINE"),
@("KFC","DINE"),
@("*PIZZA","DINE"),
@("GUZMAN","DINE"),
@("NANDOS","DINE"),
@("*PIZZERI","DINE"),
@("MISS INDIA","DINE"),
@("INDIAN FEAST","DINE"),
@("VIVIDWIRELESS","BILL"),
@("TPG","BILL"),
@("AGL","BILL"),
@("EnergyAustralia","BILL"),
@("TRANSLINK","PTRANSPORT")
)
for ($intRow = 2 ; $intRow -le $intRowMax ; $intRow++) {
$SvrName = $Rng.cells.item($intRow, $DescColumn).value2
""+$intRow+"/"+$intRowMax+" "+ $SvrName
$Rules | ForEach-Object{
$key = ($_[0])+"*"
if($SvrName -like $key)
{
$Rng.cells.item($intRow, $CatColumn).value2 = $_[1]
}
}
}
$wb.Save()
$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
code>
source to share
I was also looking for an auto-categorization process. The options above seem to be really powerful, but more complex than what I wanted.
My idea is simple: develop a set of categorization rules based on keywords. If a keyword is found in the description, the rule is applied and the category is set. Not happy with the idea of using VBA or PowerShell, continuing to look around and found the following post:
how-to-group-excel-items-based-on-custom-rules by John Bustos (please take it)
John's solution takes a very simple approach:
-
Rules are defined in two columns (Keyword - Category) - if we assume they are in columns F and G:
Column F Column G Keyword Category Starbucks Coffee shop Wal-Mart Supermarket Safeway Supermarket In-N-Out Fast Food Comcast Internet Service Verizon Mobile Phone Service
-
Then add this ARRAY formula to the cell where you want to insert a category pointing to the cell you want to check for the rule (let's say cell A2):
=IFERROR(INDEX(G$2:G$7,MATCH(TRUE,ISNUMBER(SEARCH(F$2:F$7,A2)),0)),"Other")
Remember to use CTRL + SHIFT + ENTER to make sure it goes into the array formula. If you have more rules, you will need to change the height of the range. After that, you can simply fill out the formula for all the rows that need to be classified. Also, the categorization uses the first rule and sticks to it, so if you have two different keywords present in one of the target cells, the first keyword categorization rule will be applied. Rules must be created manually, when the cell shows "Other", it means that no keywords were found.
Finally, the loan belongs to John Bustos, it was he who provided the solution here . I found his solution to be simple and extremely easy to implement, so I wanted to include it here because a search for "automatic categorization in excel" didn't come up with it immediately. I had to try other search words.
source to share