Best Free Fraud Analysis Excel Tools

[MR2sRFun]

I would like to know if anyone knows where to find free fraud analysis tools online. Whenever I perform searches, it always seems to be pages of fee-based or shareware tools promotions. I'm looking for tools more interesting than duplicate analysis or simple chart-creation based on a range.

I've developed a few (Benford Analysis, automating IT access review using Exchange data) based on pieces of code I've picked up from various forums and sources, but I can never seem to find a good listing of no-strings attached Excel fraud tools that add significant analysis capabilities, so I'm hoping this thread will provide such a listing.

The benford tool I developed is based on the macro from this article Turn Excel Into a Financial Sleuth modified to be used on any user-selected range. I would post the macros as attachments if I had permission.

 

[MR2sRFun]

I thought I'd post access review macro since the code is small. I developed this macro to pull Outlook user data based on a user-selected range containing employee names. My department had been looking up names one by one in the Outlook address book to determine the appropriateness of user access. Instead of going through more cost/effort to develop a comparison of system access and HR data and get the necessary access to underlying tables, I created a macro that automatically pulls Outlook data (which I think sits in an Exchange server) based on a list of names. The great thing is that it works on either first, last or both names. I had not been able to find anything freely available that performed this when I searched a few months ago which is why I created it.

It relies on a an old Word macro described at GetAddress Method. I suspect there's newer methods that do something similar, but I didn't want to spend too much time/effort finding them.

Since the user form's not included, you have to define the public variables to get the sub work.

'Public variables

'Used in Gettitle macro, determined in user form 4 through checkbox
'Controls whether employees not found by macro should be found by user in auto-generated outlook box
'Should usually be set to false for large ranges since each unknown user requires user interaction

Public boolManTitle As Boolean

'Used in title lookup and determined in user form 4, used to select single column range containing names
Public rngTitle As Range

'Sub code
Sub Gettitle()


'-----------------------------------------------------------------
'Needed because title information not transferring over through Access GAL download
'Makes a call to Microsoft Word to use Getaddress function
'----------------------------------------------------------------


    'User form allows user to select range through the public rngTitle and
    'check a box to set the public boolean boolManTitle
    UserForm4.Show
    
    Dim titlerange As Range
    Set titlerange = rngTitle
                     
                     
    ActiveCell.EntireColumn.Offset(0, 1).Insert
    titlerange.Offset(0, 1).EntireColumn.ColumnWidth = 40
   
    Dim objWordApp As Object
    Dim strCode As String
    Dim strOutdata As String


    Dim c As Range
    For Each c In titlerange.Cells
    
    Dim strsource As String
    strsource = c.Text
    'Set up the formatting codes in strCode
    strCode = "<PR_TITLE>" & "," & vbNewLine & "<PR_DEPARTMENT_NAME>" & vbNewLine & "<PR_COMPANY_NAME>" & vbNewLine & "<PR_OFFICE_LOCATION>"
    
' As GetAddress is not available in MS Excel, a call to MS Word object
    ' has been made to borrow MS Word's functionality


    Application.DisplayAlerts = False
    On Error GoTo Err
    'Set objWordApp = New Word.Application
    Set objWordApp = CreateObject("Word.Application")
    strOutdata = objWordApp.GetAddress(strsource, strCode, False, 0, 0, boolManTitle)
    objWordApp.Quit
    Set objWordApp = Nothing
    c.Offset(0, 1) = strOutdata
   
    Application.DisplayAlerts = True


Label1:
    Next


    titlerange.EntireColumn.Offset(0, 1).AutoFit
    
Exit Sub


Err:
    c.Offset(0, 1) = "Title Not Located."
    Resume Label1:




End Sub

You may have to change the field titles in this snippet of code depending on your Exchange fields. (Although from what I read, these particular fields may be fairly standard across all Exchange implementations.)

strCode = "<PR_TITLE>" & "," & vbNewLine & "<PR_DEPARTMENT_NAME>" & vbNewLine & "<PR_COMPANY_NAME>" & vbNewLine & "<PR_OFFICE_LOCATION>"