Here are some useful Microsoft Excel formulas and tools which I have found to be useful in fraud investigation. These tips and tools assume some level of understanding of Excel, but are far below the level of using macros and other advanced features.

Creating a Time-line in Excel

I have identified two sources for this. First if you’re working on an older version of Excel you can use the templates at Vertex42 LLC or Mr. Excel.

If you are using a version of Excel that is the 2007 or later, you can use the template that is included in the software. For a tutorial on the function there is one available on the Microsoft website.

Link Analysis in Excel

There are a couple of ways of doing this. The simplest way is to set up a spreadsheet with set of columns that reflect things such as Person/Entity Name, Address 1, Address 2, City, Province/State, telephone number, fax number, etc. After you have populated your worksheet you can then use the Pivot Table function to see the links that may exist for example, it might identify entities that share the same fax number or address.

The Pivot Table function is found under the Data menu and although it does not provide a graphical representation (it is text/chart based), it still effective to identify links between contact points. There is commercial software available that will do a much better job of providing graphical representations of link analysis charts but on simple files or if you are on a budget that does not allow you to purchase more expensive software this solution can help.

The key is to ensure that your data is entered in exactly the same format. For example if you are entering phone numbers you must choose between the format (xxx) xxx-xxxx and the format xxx-xxx-xxxx (without the brackets on the area code). You cannot do some data entry in one format and some in the other or the pivot table will not identify the links.

In order to achieve this consistency you may wish to create sub-lists on other spreadsheets linked to drop-down menus in your main sheet. This is accomplished using the Validation function in Excel which is also under the Data tab.

One solution which I have not yet had the opportunity to test is called NodeXl which is a graphical link analysis tool built on top of Microsoft Excel. I have come across a few references on the web from sources which I regularly review, including Analysts Corner and http://www.kdnuggets.com/software/social-network-analysis.html.

Comparing Two Lists

Ever have to compare two lists of items, such as a list of vendors or a list of social network friends to see if a value exists on both lists? Check out the post on IACA Analyst Toolbox site’s by an individual named Michael Chesbro. The formula in the output column is: =VLOOKUP(A1:A10,$D$1:$D$7,1,FALSE).

You can add some conditional formatting on your output column to make the results of interest a little more vibrant.

The Find Function

This is about as simple as it gets but so very helpful. You access this function by pressing “Ctrl f”. A search box will appear that lets you search through your spreadsheet (or workbook) for a specific term. For example if you have a list of vendors in a spreadsheet and you need to see if “bad guy co.” is on your list of vendors, this is the function that you need. It is not all that useful in small lists because you can manually scan the list faster than using the function.

Another example is when you want to see how similar terms are used across multiple spreadsheets within a work book. Say for instance that you are aware that a specific payment of interest is called “fundraiser”, you may search through the entire workbook for other payments labeled “fundraiser” to see where they appear. To do this you simply hit Ctrl F, click on Options and select “within workbook.”

The caveat here is the same as the caveat on many of the other tips provided. Your data format must match the format of the entries in the worksheet or you are not going to necessarily find what you are looking for (pardon the pun).

A work-around for this is using only a part of the search term which you are likely to find included in your source data. Rather than searching for “bad guy co” which may be listed as “badguyco” or “thebadguycompany” on your list of vendors, you may just want to search for the term “guy” (without quotes of course).

Separate Terms

If for whatever reason you need to separate whole terms into separate parts which is called parsing, the “Text to Columns” function under Data on the toolbar is very useful.

For example, let’s assume that you imported some HTML code into Excel from a website you were reviewing. After sorting a few different ways you end up with a series of rows that look something like:

Column A

id=abcd
id=defg
id=lkjl
id=lkjn

For your analysis you would like to use only the id values rather than including the “id=”. To do this simply access the Text to Columns function, click other and put the equal sign in the “other” space provided. This would end up producing the following:

Column A
id
id
id
id

Column B
abcd
defg
ikjl
lkjn

Other sources

If you are a Certified Fraud Examiner and you’re interested in learning more Excel tips and tricks I would also suggest that you review the “Fear Not the Software” articles from various issues of Fraud Magazine. The articles are written by Richard B. Lanza (and occasionally other contributors) and are very insightful.

Mandatory Disclaimer: Evince Services, Inc. is in no way related to Microsoft, but Excel is a commonly used software and therefore a possible low-cost solution for some reader’s problems. NodeXL®, Excel® and Microsoft® are registered trademarks of Microsoft Corporation. Mr. Excel® is a registered trademark of Tickling Keys, Inc.

For government departments that are considering developing a forensic audit capacity I would recommend the article written by Alan Gilmore in the Financial Management Institute of Canada’s FMI*IGF Journal Volume 20, No. 1, Autumn 2008. The article is published in both English and French and is available from the Financial Management Institute’s website.

If you’re a white-paper junkie like me Open (re)Sources is a virtual goldmine. According to the site’s author:

“OPEN(re)SOURCES is a collection of current information gathered from across the Web. Resources include information relevant National Security Intelligence found in Open Source reports, analyses, briefings, articles, etc. Topics regularly cover terrorism (both domestic and foreign), Cybersecurity, Political and Military Analyses, Environmental Issues/Impacts and Analytic Methods and techniques. Items will be posted every Thursday. While the blog posts reflect a date range, resources within those posts may be older than the indicated range; the range purely reflects the time period in which I found the resources.”

I discovered it via Analysts Corner, also a great resource.

Anyone looking for a primer on Internet Investigations will want to read the white-paper written by Todd Shipley of Vere Software called “Collecting Legally Defensible Online Evidence: Creating a standard framework for Internet Forensic Investigations.” It provides some very good fundamental points on the collection, examination, analysis and reporting of online evidence. The document was produced by a software company but it is vendor neutral and couched in U.S. case law. The references in the back of the paper are also very useful.

I would suggest that the framework provided in this document, augmented with some of the more recent developments in the legal and academic communities dealing with this field (such as protection of the identities of third-parties who are captured or referenced in on-line evidence that are not parties to the “offence”, developments in the law with respect to evidence obtained from social networking sites and Web 2.0 platforms, privacy concerns in different jurisdictions internationally (admittedly as a Canadian I always like to see Canadian content included) etc.) would be very valuable….any takers?

Also, can we solve the debate once and for all? Is it “Internet Forensic Investigations” or “Forensic Internet Investigations”? My vote is for the later.