Category Archives: Data Analytics

Shatter Silos to Identify More Risk

If you want to increase the effectiveness of your audits and find risks that haven’t been identified before, you need to shatter your silos so you can identify more risk.

Too often, audits are performed on one process, one category, or one system: Earning Commissions, Windows Servers, or Wire Transfer. Each one of those is a separate silo (one for oats, one for corn, one for rice).

Continue reading

Leave a comment

Filed under Audit, Data Analytics, fraud, How to..., Technology

ACL Table Already Open error message

acl table leggyOh, I hate the Table Already Open ACL error message with a passion.

And the Command Cancelled message (see the end of this post regarding that message).

Usually, it means I did something stupid, and I can figure out what, and fix it pretty fast.

Sometimes I have to scratch my head for quite some time before I figure it out.

I wrote a post in 2017 about Deleting ACL Table Covers A Multitude of Sins. This post is an expansion of that post, but mainly focuses on the “Table Already Open” error.

Continue reading

2 Comments

Filed under ACL, Audit, Data Analytics, How to...

My Favorite ACL Tricks

Here’s a couple of my favorite ACL tricks & treats that I use frequently to get me through the day a little faster and a little less frustrated.

These tricks are the kind that they don’t teach you in class or in tutorials (at least I’ve never learned any of them there; maybe I was in the bathroom during that session); I either figured them out on my own or had someone say, “Let me show you something.”


The Command Line

When I train someone in ACL, the command line is one of the first bonus items to which I draw their attention. The command line allows you to run individual ACL commands without using the ACL menu or scripts.

To open the command line: in the menu bar, click Window, Command Line. This will appear:

You can run most ACL commands from the command line, such as OPEN a table, ASSIGN a variable value, and lots more (the commands can be entered in lower/upper/camel case, but I use uppercase in this post to help them stand out).

My 2 most frequently used command are listed below.

DISPLAY – list the fields in a table, along with their start position, length, and more.

To run this command, 1) open the table you want to run this command against, and 2) enter the command in yellow in the command line, and press Enter.

Note that the last line shows you a computed field and the formula behind it.

DISPLAY VARIABLES – list all currently active variables, their type/format, and their values.

To run the following command, just enter it in the command line, and press Enter.

Note that user-defined variables (v_record and v_table) are shown, along with system variables (OUTPUTFOLDER and WRITE1). If you’re not familiar with ACL system variables, look them up in the ACL help file (it will be worth your time).

Note that 2 of the variables are character (C) type and 2 are numeric (N).

This command is extremely helpful when you are troubleshooting variables.

Bonus: Instead of DISPLAY, you can type DIS; instead of DISPLAY VARIABLES, you can type DIS VAR. Much shorter!

Bonus #2: Another useful use of the command line is to enter variable values. For example, if you have a NOTIFY command at the end of a script that will send an email if v_Run_Notify = “Y”, you can enter v_Run_Notify = “N” in the command line and press Enter to change the variable value and prevent the NOTIFY command from running while you test changes to your script.

Open a Table You Can’t Find

Sometimes I can’t find a table because I don’t remember (or know) which ACL folder it is hiding in (the folder in your project, not a Windows folder on your hard drive).

If you know the name of the table, you can just type OPEN <tablename> and press Enter (where <tablename> is the name of the table you want to open). When I don’t remember the table name or I’m too lazy to type it out, I copy the name from the ACL log or a script that uses it, and copy it to the command line.

When the table opens, you can then see what folder the table was hiding in (the folder is not shown in screenshot below).

Clear the Command Line

When you use the command line a lot, you have to clear it before entering another command. Instead of backspacing and deleting the text, or highlighting and deleting the text, just click the X at the far right.

Likewise, instead of pressing Enter after entering a command, you can click the checkmark.

Table History

When you’re working on a big project that contains many different tables, sometimes it’s hard to remember how that table was created. Or you haven’t opened the ACL project in a while, or you have to troubleshoot or review a project someone else created.

So what table(s) were used to create that table, and what filters/joins were used to create it? How many records did the original table contain?

I used to hunt through the ACL log or the scripts to find all that info, but for the most part, it’s all in the table history.

To access a table’s history, 1) open the table you’re interested in, and 2) from the menu bar, select Tools, Table History. You’ll see something like this:

The first line shows the original table (PcardTransactions) and the FILTER used. The second line shows the filtered data (all fields) was extracted to a new table (PCardUSA).

The third line shows number of records in the original table (Input) and the fourth line shows the number of resulting records (Output) in the extracted table.

If a JOIN was used, the table history would list the primary and secondary tables as well as the JOIN command parameters used.

The other nice thing is that you can take a screenshot of the table history and use it for documentation or evidence.

Bonus: Instead of selecting Tools, History from the menu, you can type DIS HIS in the command line, and press ENTER. Same results!

If you have some ACL tricks up your sleeve, let me know.

5 Comments

Filed under ACL, Audit, Data Analytics, Free, How to..., Scripting (ACL)

Quote of the Weak: No end goal

The other day I was in a meeting to discuss a new analytics project and discovered the team had no end goal.

When the discussion started with the software to be used, I knew they were already off track.

Continue reading

4 Comments

Filed under Audit, Case Files, Data Analytics, Humor/Irony, Quote of the Weak

How to get a Data Science job with little or no experience

data scientistWhen you’re trying to get a data science job, you need experience, but to get experience, you need a job, right? Not always, and this is the case for many jobs, not just data science.

But in data science, you can generate the experience you need yourself.

You might have seen one of my earlier posts, How to get an IT Audit job with little or no experience. Let me say from the beginning that getting an IT audit job with no experience is easier than a data science job with no experience. But according to an article from KDnuggets, it can be done. And like everything else, it takes hard work.

The article defines data science as “an interdisciplinary field that focuses on solving problems and gathering information.” 

Continue reading

Leave a comment

Filed under Audit, Blogging, Data Analytics, Data Science, Employment, Free, How to..., Technology

Another Nail in ACL’s Coffin

Diligent’s acquisition of Galvanize (ACL) is another nail in the ACL analytics coffin.

First, ACL acquired another company and created Galvanize. And we were told governance, risk, and compliance (GRC) would never be the same.

And I told you that ACL analytics would never be the same. In fact, I predicted that this acquisition meant that ACL analytics was dying (when I say ACL analytics, I’m referring to the Windows desktop version that they built the original company on).

For more on this, see ACL Officially Changes Name & Spots  and Is ACL Analytics Dying?

Continue reading

11 Comments

Filed under ACL, Audit, Data Analytics, Scripting (ACL), Technology, Written by Skyyler

Critical Thinking? How about just Thinking?

It seems to me that auditing as a profession is not full of critical thinkers, much less thinkers.

If you read my last post about auditor judgment, I’m struggling with some of the junior auditors that I’m working with.

But I’m also struggling with quite a few of the senior auditors that I work with, those that are my peers (which means they peer at what I’m doing and how I’m doing it and then continue on their merry paths).

I came to this opinion based on most of the auditors I’ve met through the years across many companies, small and big, and across sectors, including public service. And also by the many articles calling for the profession to do more critical thinking, and yes, it is needed. 

But let’s start with plain old thinking (walk before run).

Continue reading

4 Comments

Filed under Audit, Data Analytics, Excel, Humor/Irony, Technology

Quote of the Weak – Auditor Judgment

We recently acquired a new data analysis tool in our department, which prompted some of our newbie auditors to share their misunderstanding of auditor judgment and basic data analysis.

A group of less experienced and newer auditors were selected to try out the new tool before it was rolled out department-wide.

 If you’re not familiar with my ‘Quote of the Weak’ series, I described it briefly in About. For a list of posts in this series, see here. If you haven’t seen one of these posts before, it’s because I haven’t had one in a while…

Continue reading

4 Comments

Filed under Audit, Data Analytics, Humor/Irony, Quote of the Weak, Technology

Create a Help Desk for Data

analytic metrics, numbersCompanies need to create a help desk for data, similar to the help desk they created for hardware, software, application, network, and user problems.

Can you imagine if companies didn’t have a computer help desk and each department had figure out their own computer issues? If each department had to find, load, configure, and troubleshoot their own hardware and software?

But isn’t that how most companies operate when it comes to data and data projects?

Continue reading

Leave a comment

Filed under Audit, Data Analytics, How to...

Mack-the-Auditor Gets Audited! Part 3

Review ACL log

This is the third of 3 posts; this post describes how I audited the auditors and my perspective on the whole thing.

Read the first post (background) and the second post (audit results).

Continue reading

1 Comment

Filed under ACL, Audit, Case Files, Data Analytics, Scripting (ACL)

Mack-the-Auditor Gets Audited! Part 2

Review ACL log

This is the second of 3 posts; this post describes the audit, some speed bumps, and the audit results.

Read the first post here, which provides the background on the audit and the audit’s scope.

Continue reading

1 Comment

Filed under ACL, Audit, Case Files, Data Analytics, Scripting (ACL)

Mack-the-Auditor Gets Audited! Part 1

Review ACL logUsually, I’m the one doing the auditing, but this time, I (Mack) was the one who was audited.

It was a great experience for me.

Well, sort of. No one likes being audited (ahem). But it gave me a fresh perspective of how others feel when I audit them.

This is the first of 3 posts; this post contains some background info on the project that was audited, and the second one discusses the audit and the results, and in the third post, I describe my perspective on the whole thing, and some takeaways.

Continue reading

1 Comment

Filed under ACL, Audit, Case Files, Data Analytics, Scripting (ACL)

Auditor Struggles, Part 4

This is Part 4 of a Case File series that describes how real auditors tried to apply questionable methods to auditing and data profiling. See Part 1, Part 2, Part 3.

Does the Process X team provide metrics around their process?” I asked.

“Yes,” the most senior auditor replied, showing me the web page where the Process X metrics were displayed.

After reviewing the page briefly, I said, “I see they do metrics by month. You have a year’s data; are you planning to understand how they prepare their metrics and re-calculate them to see if you get the same numbers?”

Continue reading

Leave a comment

Filed under Audit, Case Files, Data Analytics, Excel

Auditor Struggles, Part 3

This is Part 3 of a Case File series that describes how real auditors tried to apply questionable methods to auditing and data profiling. See Part 1 and Part 2.

I looked at the third page of the handout and asked, “What is this?”

“A list of Active Directory (AD) groups and the user IDs in each group. I searched AD for any group containing the system name,” the junior auditor said, “and identified these 6 groups. I then downloaded all the members of these groups from AD into Excel.”

Continue reading

Leave a comment

Filed under Audit, Case Files, Data Analytics, Excel

Before You Analyze Data

Before you start analyzing data, you need to 1) know you have the right data, and 2) understand the data and the process that produced it.

This post assumes, of course, that you already accomplished some of the hardest tasks already: figuring out what data you need, where to get it, and actually getting the data. Good luck with that. :)

This post is part of the Excel: Basic Data Analytic series.

Continue reading

Leave a comment

Filed under Audit, Data Analytics, Excel, How to...

How to Profile Data

Before you analyze data, you should profile it.

Otherwise, your analysis may not be too broad, too narrow, or you may miss some important insights or errors.

This post is part of the Excel: Basic Data Analytic series.

Data profiling is developing a profile of your data, just as facial profiles of a person, taken from various angles, helps you size up a person’s nose, identify whether his chin is sagging, and how far apart the person’s eyes are.

Continue reading

4 Comments

Filed under Audit, Data Analytics, Excel, How to...

Is ACL Analytics Dying?

Spiral deathI fear that ACL Analytics is dying, and has been as long as I’ve been ranting about it.

Making Laurie Schultz their CEO helped, but I don’t think it has been enough.

NOTE: I wrote this well over a month ago, long before I posted the ACL Officially Changes Name & Spots post; I just got sidetracked and forgot about this post. I stumbled across it today in my Drafts folder. I decided to publish it ‘posthumously’ (so to speak) to show 1) how much I’m agonizing over ACL’s direction, 2) how I’ve always felt about ACL’s software, and 3) provide some balance to my previous post.

Continue reading

19 Comments

Filed under ACL, Audit, Data Analytics, Written by Skyyler

ACL Officially Changes Name & Spots

It’s official: ACL is changing its name AND its spots.

I’ve claimed several times that ACL has left its first love (analytics) and doesn’t put enough work into their flagship product, ACL Analytics.

Correction: their FORMER flagship product.

At least they are publicly admitting it finally–they NO LONGER are an ANALYTICS company!

Continue reading

10 Comments

Filed under ACL, Data Analytics, Excel, Technology, Written by Skyyler

ACL Desktop Gone in 5 Years?

Rumors have it that ACL will no longer be available on the desktop (laptop, or other local machine) in 5 years.

That is, according to an ACL user who attended the 2018 ACL Connections conference.

Continue reading

3 Comments

Filed under ACL, Audit, Data Analytics, Scripting (ACL), Written by Skyyler

Quote of the Weak – Clean Data Manually

clean data manuallyIf you are in IT, audit, or security (or any other job requiring data analysis), you should NOT be cleaning data manually.

Let me share a recent experience with you….

A young IT auditor texted me at work and asked for some Active Directory user account data that I capture automatically every week, using some scheduled ACL scripts.

If you’re not familiar with my ‘Quote of the Weak’ series, I described it briefly in About. For a list of posts in this series, see here.

Continue reading

3 Comments

Filed under Audit, Case Files, Data Analytics, Excel, How to..., Quote of the Weak, Security, Technology

ACL Robotics is NOT Robotics

RPA the robotContrary to what ACL has been touting as their new ‘robotics’ feature, it is NOT robotics process automation (RPA).

[The ‘robotics’ feature is due out later in 2018. It appears to be ACL’s latest attempt to get you to use their GRC software.]

ACL, via John Verver, defines the term this way in his RPA article: “The idea is a relatively simple one: get computers to perform tasks normally performed by humans, and cut resource and time requirements for many repetitive activities.” Continue reading

4 Comments

Filed under ACL, Audit, Data Analytics, Scripting (ACL), Technology

ACL Tip: Be Careful when Renaming Tables

acl table leggy




When you need to rename ACL tables, be careful to also rename the associated .fil file also.

Otherwise, you (or your ACL script) might get confused. You might delete the wrong table or .fil file, and create a head-scratching problem.

I know because I confused myself.

Continue reading

Leave a comment

Filed under ACL, Audit, Data Analytics, How to..., Scripting (ACL)

Steal from Agile to Increase Audit Analytics

agile analyticsTo increase the amount and depth of the analytics performed, steal some agile methods, and apply them to your audits.

If you’re not familiar with agile methods, check out the first 5 topics listed here (just click Next at the bottom of each page; the topics are quick to the point and full of pictures).

Briefly, agile projects are performed in cycles, or iterations, rather than in a long, linear-waterfall fashion, which is: do all planning, then field work, then reporting. Each iteration of the project creates some value and includes feedback, which is used in the next iteration to increase the value of the project.

Continue reading

1 Comment

Filed under Audit, Data Analytics, How to..., Technology, Written by Skyyler

Kyle and a Conversation about Analytics

kyle bitsA while back, a reader named Kyle and I had a conversation about analytics.

It started with his reading my Excel:Basic Data Analytics post where I list a number of procedures that anyone can do in Excel.

Kyle said he was expecting some “super sophisticated process & methodology that works like magic.”

Continue reading

Leave a comment

Filed under Audit, Data Analytics, Technology

Create a Team for Audit Analytics? Part 3

analytics team?In the previous post, Create a Team for Audit Analytics? Part 2, I explored the pros and cons of expecting all auditors to develop a level of data and analytic proficiency.

These auditors would continue to do audit testing that involves analytics as well as testing that does not involve analytics. In addition to keeping up their business skills, they would be learning and upgrading their data analytic skills.

In the first post of this series, I reviewed some of the pluses and minuses of creating a dedicated analytics team.

However, a third option exists, which is sort of a hybrid between having dedicated analytic auditors doing all the analytic work and requiring everyone to increase and develop their data and analytic skills.

Let’s explore the hybrid method in this post, and wrap up the series with a few final thoughts.

This is the third post of a 3-part series…

Continue reading

7 Comments

Filed under Audit, Data Analytics, How to..., Technology, Written by Skyyler

Create a Team for Audit Analytics? Part 2

analytics team?In the previous post, Create a Team for Audit Analytics? Part 1, I explored the pros and cons of developing an analytics team.

This team consists of analytic auditors who are dedicated to analytic projects; they would NOT typically manage audits or testing that did not include analytics.

In this post, let’s explore another option for managing and growing analytics in an audit department — expecting all auditors to develop a level of data and analytic proficiency.

This is the second post of a 3-part series…

 

Continue reading

1 Comment

Filed under Audit, Data Analytics, How to..., Technology, Written by Skyyler

Create a Team for Audit Analytics? Part 1

analytics team?Once your audit team has proven the value of doing analytics consistently, the next question is: Do we create an analytics team and have the team do all (or the majority) of the analytics?

Or should we expect all auditors to develop some levels of analytics proficiency?

Of course, this question often comes a bit further down the trail on the analytics journey, but I think the sooner it is decided, the better.

This is the first post of a 3-part series…

Continue reading

2 Comments

Filed under Audit, Data Analytics, How to..., Technology, Written by Skyyler

The Analytic Staircase for Auditors

analytic staircase stepsBuilding a successful audit analytics program is like climbing a staircase.

The staircase is a set of steps that consist of several items having increasing levels of maturity.

The staircase steps not only help you build your program, but enable you to measure that maturity.

As you view the staircase graphic, mentally insert the word “analytics” before each step.

Continue reading

3 Comments

Filed under ACL, Audit, Data Analytics, How to..., Technology, Written by Skyyler

5 Things We Need from ACL in 2018

5 thingsHere’s the 5 things I’m hoping will change in 2018 regarding ACL.

They are all related to each other and feed off each other…

Interesting.

Continue reading

9 Comments

Filed under ACL, Audit, Data Analytics, Excel, Scripting (ACL), Technology, Written by Skyyler

Quick Introduction to ACL

If you’ve ever wondered what Audit Command Language (ACL) is, here’s a quick way to find out.

ACL has provided a quick, one-page introduction to ACL. And I mean quick.

It doesn’t explain a lot, but it gives you a quick peek at the basic user interface.

You could call it the ACL Overview for Dummies.

Continue reading

3 Comments

Filed under ACL, Audit, Data Analytics, Technology

IIA Analytics Article Dead Wrong

analytics dead wrong iia tombstoneA recent IIA article on building an analytics function in internal audit is dead wrong.

At least on one major point, anyway. And it’s a big one.

As the tombstone reads, this point is D.O.A (dead on arrival, or more specifically, dead on analytics).

The article, Building a data analytics program, requires IIA membership to view, and is located at https://iaonline.theiia.org/2017/Pages/Building-a-Data-Analytics-Program.aspx (that’s actually good, as it means a lot fewer people will ever read it).

Continue reading

4 Comments

Filed under Audit, Data Analytics, Written by Skyyler

Analytics Blog Debate Heating Up

A debate on this blog over analytics and the future of internal audit is heating up.

A few readers, including our colleague across the sea, AuditMonkey, have dove in, and skyller and I have responded in kind.

Well, not exactly. AuditMonkey has been more kind, to his credit. But I digress.

Continue reading

Leave a comment

Filed under Audit, Data Analytics, Employment

No Analytics, No Audit Department

dead-audit-department

If YOUR audit department doesn’t embrace data, analytics, and automation eventually, your audit department will NOT exist.

No data, no analytics. No analytics, no automation. Eventually, no audit department.

Editor Note: This post really applies to all departments in a company, but mainly I’m addressing auditors, but you might want to read between the business lines….

By embrace, I don’t mean have one or two auditors working on this. I mean the entire department.

Before you cite all the regulatory requirements mandating the existence of an audit department in companies, having an audit department in name only won’t cut it.

Having an inept audit department will not be acceptable to regulators, and it shouldn’t be acceptable to company management either. Or Audit Committees!

Companies need skilled and efficient auditors that can do the heavy lifting, and this need will only increase.

Continue reading

17 Comments

Filed under Audit, Data Analytics, Employment, Technology, Written by Skyyler

Audit Automation is NOT all Automation

audit automation ACLSome Chief Audit Executives (CAEs) and audit managers tend to think that audit automation is a set-it-and-forget-it process. NOT.

In this post, I want to expand on a problem I mentioned in an earlier post , 10 Signs Mgmt Doesn’t Really Support Analytics.

Audit management too often thinks that once a process or an audit is automated, ALL auditor/staff hours previously spent performing that process can be reassigned elsewhere.

That is not the case at all.

Continue reading

3 Comments

Filed under ACL, Audit, Data Analytics, Scripting (ACL), Security, Technology, Written by Skyyler

No Metrics, Little Analytics

analytic metrics, numbersIf your department doesn’t track metrics on your analytics, you are probably not doing analytics or you are making little progress in analytics.

In either case, its obvious that analytics isn’t very important to your management.

Which is one of the points I made in my post, 10 Signs Mgmt Doesn’t Really Support Analytics.

So far, I have encountered very few audit departments that track meaningful metrics about their analytics.

Counting the number of projects that include analytics isn’t enough.

Continue reading

1 Comment

Filed under Audit, Data Analytics, How to..., Written by Skyyler

Require Analytic Skills to Hire and Promote 

up down arrowsUnless your department is still in the early stages of your analytics journey, analytic skills should be one of your hiring and promotion criteria.

In an earlier post I outlined 10 Signs Mgmt Doesn’t Really Support Analytics.

One of the signs is that hiring and promotion decisions are made without reference to a person’s analytic skills.

Continue reading

Leave a comment

Filed under Audit, Data Analytics, Employment, How to..., Written by Skyyler

Which Way is Analytic North?

compass analyticsTo create a successful analytics program in internal audit, you must have a plan. A plan that points to analytic North.

That requires WRITTEN goals.

In an earlier post I outlined 10 Signs Mgmt Doesn’t Really Support Analytics.

One of the signs that indicates management isn’t really serious about analytics is that management does not require every staff member to have measurable analytic goals.

Continue reading

Leave a comment

Filed under Audit, Data Analytics, Excel, How to..., Written by Skyyler

Dedicate an Analytics Champion

analytics leaderTo create a successful analytics program in internal audit, you must dedicate a person to serve as an analytic champion.

In an earlier post I outlined 10 Signs Mgmt Doesn’t Really Support Analytics.

The first sign I noted was the lack of a full-time analytic champion.

Continue reading

7 Comments

Filed under Audit, Data Analytics, Written by Skyyler

10+ Signs Mgmt Doesn’t Really Support Analytics

mgmt doesn't support analyticsYour management says it wants more analytics, but does it really support analytics? Here’s 10+ signs that indicate that your mgmt:

  • Does NOT knows what it takes to get analytics off the ground
  • Believes that analytics multiply like rabbits, naturally
  • Is NOT willing to make the adjustments required to deliver and sustain real value.

Continue reading

5 Comments

Filed under Audit, Data Analytics, How to..., Technology, Top 10, Written by Skyyler

Why Internal Auditors Should Care about Robotic Process Automation

3 Comments

Filed under Audit, Data Analytics, Employment, How to..., Technology

Real Auditors Use Excel PowerPivot

powerpivot iconIf you’re an auditor and you are not yet using Excel PowerPivot, you are missing the next greatest thing since spreadsheets arrived.

If you are NOT an auditor, and you don’t use PowerPivot, you’re in the same boat with the auditors mentioned above, and it is sinking.

In other words, if you use Excel, you should be learning Excel PowerPivot. It’s that big.

Let me explain why.

NOTE: I updated this post quite a bit with new info…

Continue reading

13 Comments

Filed under Audit, Data Analytics, Excel, Free, Technology

Auditors, Do Data Analytics or Die

If you’re an auditor, you need data analytic skills or you will die.

Or put another way, if you don’t acquire them in the next 1-5 years, you will no longer be an auditor.

Pretty bold statement, isn’t it?

Continue reading

10 Comments

Filed under Audit, Data Analytics, Employment, Free, Technology, Written by Skyyler

ACL Import Fails, No Error?

import errorToday I was adding a new table to a scripted ACL project and kept getting an error.

This project automatically opens a folder on the LAN, reads the files in the folder, and loads all of them.

All I did was add one more file to the folder. ACL refused to load that one file.

Syntax error.

WTS?

Continue reading

1 Comment

Filed under ACL, Audit, Data Analytics, Scripting (ACL), Technology

How to Review Your ACL Log

Review ACL logWhether you script your projects or use menu commands, you need to review your ACL log carefully.

Good analysts review their results and the log as they work in ACL, after they think they are done, and have others review their log before the ACL project is relied upon.

(You can’t imagine the dumb mistakes my team and I found that saved us a lot of embarrassment later.)

Continue reading

3 Comments

Filed under ACL, Data Analytics, How to..., Scripting (ACL), Written by Skyyler

Transform Data Fast with Excel Flash Fill

Excel Flash Fill, the un-formula filler, formatter, and concatenatorYou can easily use Excel’s Flash Fill tool to transform data fast, without formulas.

Did you catch that? Without formulas!

Flash Fill has been around a few years, but few people, including auditors, seem to be aware of it.

This tool is so easy to use, you could learn it AND teach it to your mom in 4 minutes. Really.

Continue reading

5 Comments

Filed under Audit, Data Analytics, Excel, How to...

Dilbert Does Big Data

Dilbert does Big Data
If you like Dilbert cartoons or big data, you might enjoy Dilbert’s adventures in data analysis, data mining, data privacy, security, and dealing with a dumb manager.

Continue reading

Leave a comment

Filed under Audit, Data Analytics, Humor/Irony

Check Excel Data for Blank and Invalid Values (Part 2 – Sort)

basic data analytics1As I explained in the last Excel post, you can check for blank and invalid data in Excel several ways.

In this post, I will focus on the insights and issues encountered by sorting each column from A to Z and then Z to A.

Sounds pretty simple, but I’m willing to bet you will be surprised to learn a thing or two…

For a list of the reasons why you must validate data before analyzing it, see Why You Must Validate Data.

Continue reading

2 Comments

Filed under Data Analytics, How to..., Technology

Check Excel Data for Blank and Invalid Values (Part 1 – Dropdown)

basic data analytics1You can check for blank and invalid data in Excel several ways.

Depending on the size of the file and your preferences, you can either scroll through the dropdown list, sort each column from A to Z and then Z to A, or apply a filter.

Sometimes, you need to use a combination of these methods.

It’s important to know how these methods treat data differently and to be aware of their limitations.

Continue reading

3 Comments

Filed under Audit, Data Analytics, Excel, How to...

Why You Must Validate Data

basic data analytics1Before you analyze data, you must first validate it.

Otherwise, your analysis may not be accurate, and you may miss some important insights or errors.

This post is part of the Excel: Basic Data Analytic series.

Before analyzing your data, you need to check the following:

  • Duplicate transactions do not exist.
  • Required fields/key fields do not contain blanks, spaces, zeroes, unprintable characters, or other invalid data.
  • Date fields contain real dates, and the range of dates is appropriate.
  • Amount fields don’t contain inappropriate zero, positive, or negative amounts, and the range of values is appropriate.
  • Each field is stored in the correct format. This prevents data from being converted on the fly into something else unexpectantly (e.g., user ID JUL15 becomes 15-Jul).

Continue reading

4 Comments

Filed under Audit, Data Analytics, Excel, How to...