- When you're comparing one set of numbers to another set with key field (required) or multiple key fields, theoretically, you'll end up with numbers that equal each other, except for those that don't. So a graph is generated automatically that shows the amounts that match... They basically form a 45 degree line. Those that do not match fall outside the line. You can mouse over any point to see what's off.
- If you're looking at only one set of data because you want to find out certain things about the data. It's not a reconciliation situation. Like let's say you want to check for strange activity in vendor accounts. So you can upload a table with disbursements. Each line will have an amount and a vendor. Crush will create something like an Excel Pivot table that summarizes the disbursements by Vendor. But Crush will also show a graph that plots the number of vendor transactions against the sum of all of the transactions. So you can quickly isolate the vendors with infrequent payments, but big amounts. Crush also will highlight Vendors that are paid lots of little checks and one big check. So you'll be able to see how they will Skew.
- When you sum up a bunch of items by a key field, like a vendor, you can click on a popup that displays all of the rows that sum to that key field. It's very similar to a drill down on an Excel pivot. You can now click a link that will display a Histogram of those numbers. So you can quickly see if there is something strange going on.
Blog for Power Sheet AI BI for Excel
I used the google graphics engine to add graphs in key places. There are essentially 3 places where graphs are placed.
Working with perfectaudit.com to deliver a service that will automatically convert pages and pages of PDF statements into CSV files that crusherrors.com can use.
Update November 15, 2016
I used the service for a client. I was only able to obtain PDF's for first 8 months of 2016. It converted it to a CSV flawlessly. It is an amazing service.
THE MANUAL AUDIT PROCESS
Upload documents to be digitally analyzed.
Investigate analyzed financial documents with precision.
Top-of-the line security standards.
Streamline the auditing process.
There are many new updates that have not been blogged about yet, but this one is so simple and SO HELPFUL, that it had to be posted. Have you ever compared two bits of text and they look IDENTICAL but one is slightly different because there's a hidden space. CrushErrors has been able to handle those differences for about 9 months, but now it highlights the difference in a hidden space so clearly.
Below you'll see a video that shows how "Cash - Wells Fargo" is scored against "Cash- Wells Fargo". The score is 95.8%. But it looks like it should be 100%. There's an extra space on the right side. If you click the text in the Redline column, the box labelled "Detail for Fuzzy Match" will appear to show the details. The Redline row clearly shows there is an extra space.
Another annoying error Crushed!
See it in action with this App
This is a very rough draft right now. But it does give a sense of the richness of Apps.
This process so far should definitely be less than 10 minutes. The hardest part should be getting the password for the PayPal account.
Now with everything loaded into CrushErrors.com, you can press the Compare button, but the results will be a mess. It's a mess because you probably have more data than you need. You probably have many cleared transactions. You need to focus on the uncleared ones. So on the PayPal side, you need to make sure you're only looking at the rows for the month you're trying to reconcile. Let's say May. On the QuickBooks side, you may have rows for March and April. You need to compare ONLY the month at PayPal and only the uncleared records in QuickBooks.
So to look at only those rows, you need to filter on them. You can set up those filters one step at a time in Crush. With an app, you set up the app once and then reuse it. You may want to hide some columns. Again, you can do that manually, but an app let's you redo it over and over without doing much manually. So with the proper data and the proper app, you can do a reconciliation in a few seconds. That's literally true if there are less than 1000 transactions on each side. Hundreds of thousands of rows could take 10 minutes.
The above video shows a real PayPal reconciliation with app that are already set up. From paste to finding the errors is less than 40 seconds. The video shows that and then goes into detail on each line of the app.
Now it's very simple to use. The video below doesn't do it justice. The only way to really appreciate the power of it, is to try it. Here are the steps. You copy and paste some data. It scans the data and anticipates what you're trying to do. It suggests columns to add. It suggests filters to apply. (You can also drag and drop a CSV file.)
Then there's a simple interface to match up key fields to optimize the results. You can keep experiment until you get the results you need.
It's hard to convey the power of this SETUP WIZARD. Let's look at the big picture. The process of finding errors is hard work. It's mostly done entirely manually because it's a big challenge. It's hard to match up two sets of data on a key field and find the differences. A very technical person can automate it in Excel. A less technical person pretty much must do it manually.
So the challenge is how do you make a software solution that will work with the less technically proficient user. In today's world, everyone is very comfortable in a browser. And less comfortable with Excel. So the browser as a software platform is a good solution.
Initial work on Crush deployed many features. First there was a comparison engine. Second, there was a way to line up data on key columns. Third, summation of balances were added.
But then it was clear that some columns needed to be added, like a column that would subtract CREDITS from DEBITS. Obviously, this formula could be added in Excel. But the goal has always been to AVOID work in Excel and ideally work directly from a CSV. If Crush depended on the user making changes in Excel, the value of the system would be reduced.
Then it was clear, more was needed. It was essential to filter out certain rows. For example, a CSV from a bank could include THREE months of data, but to do a monthly bank reconciliation, only ONE month could be used. Again, those other two months could be deleted in Excel, but again, that is not the ideal.
Recapping, there are lots of pieces to do a reconciliation. A difference engine. A key column engine. A summation engine. A way to add columns. A way to filter rows.
That's a lot to handle. So the next step was to build a recipe system that would tell Crush what steps to take -- step -- by -- step. That really worked very well, but it is essentially a feature that would require yet another level of technical know-how. But for technically oriented users, it is great. It essentially automates all the steps needed to Crush errors.
So the missing piece -- that is finally done -- is the SETUP WIZARD. Now the NON-TECHNICAL person is guided through the process step by step to find errors.
There's a lot of artificial intelligence. First, it eliminates columns that are essentially empty. Users tend to put in empty columns between real columns. That makes the system a little messy.
Then it looks for columns that are named Debit, Credit and dozens of other standard column names. It then figures out what columns you may need to add. It asks you to do that. It then asks which rows you need to filter. RIght now, it only filters by month or the CLR column. The CLR column is produced by QuickBooks. If there's an asterisk in the column, then it should be included in a reconciliation.
Then it examines columns that have the same name and it assumes they are key columns. But it will also pair Amt with Amount.
Then it color codes each column. Columns with number values are green and have a green box. Columns that are added by the system have a blue box. Crush makes suggestions as to how to organize the fields. But users can now easily modify the suggestion by dragging fields to the right box. A Key field, like Customer, could be dragged to the Key box. An unimportant field, like zip code, could be dragged and dropped into the Ignore section. To drag all the fields, hold down the ALT key.
So to recap. There's the artificial intelligence that added columns and filtered rows There's the intelligence that recommends how to organize the fields. There's the easy user interface with color coding and drag and dropping.
But the last new essential feature is that there is a TEST button. So you can move a field and then TEST the results. Then there's a popup that appears with the results. Then try another combination of fields. And then do another test. Compare the results. Every popup has a button that will restore the combination of fields to the one that generated those results.
So users can keep experimenting. Yet every experiment keeps track of all the steps. And displays the results. So you can restore your settings at any time to the same settings that produced those results.
It is now an integrated solution with all of the power required to solve a hard problem and the ease of use that makes it accessible to all.
Sometimes it's hard to explain what a Fuzzy match is, so here is an example. Anyone who is trying to compare data in Excel, for example, is almost always limited to exact matches. In other words. Doug must equal Doug and Doug will not equal Douglas. But fuzzy matches are different. See below. CrushErrors.com will correctly match Tom with Thomas and Dave with David. But that's not really enough. It's ok if you only have 4 rows as shown below. But what if you had 1000 rows. You need to see which matches make sense. Because it's fuzzy, it almost means that it's sometimes wrong. So there is a score (see orange arrow belowthat indicates how close the match is. Tom and Thomas are more different than Ben and Benny.
All efforts in last few months have been on the product. Hardly any effort has been on the website or marketing for that matter. The product has many new features that will be revealed in further detail in the weeks to come.
Briefly, it can now handle CSV files. You can drag and drop them into the text boxes.
There are now progress bars, so you can process big files. 12 MB. No problem. Must be CSV.
There is now Fuzzy comparison. So you can match Doug with Douglas or Dave with David.
Oh, and dedupe also does a fuzzy search, so you're much more likely to find dupes.
Much more to come..
After this latest update, that gives users the power to MERGE and compare two sets of data by row. It even allows users to line up the results, Reconciliation on Fire is really reaching an entirely new level.
Up to this point, the service made it much easier for Excel users to find discrepancies and differences. The word "much" doesn't do it justice. It's more like much much MUCH. That's great, but it was still more for quote-unquote Excel users. Even a serious pro would find value. The non-pro may have preferred to stick with stare and compare and tick and tie methods of finding discrepancies.
But this is another level.
Now just about every user can stop manually finding differences. This is now too easy. It's too too easy. If you're comparing anything, you'll quickly see what matches and what doesn't. Row by row is another level.
With a browser, it's so fast. It's fast to learn. No installation. See results in minutes.
It will empower people to automate things they've never automated. And even very heavy Excel users will be able to produce faster results.
This really is a big update. It's almost impossible to do this task in Excel. It would require many many steps including a complex pivot table and appending databases.
Ok. So here's a scenario and you'll see quickly what this can do.
Let's say you have a chart of accounts from a few weeks ago but the accounting team are making lots of changes. They're renaming account. Adding accounts. Deleting accounts.
Many of the balances are the same, but they could also be different.
Here's what you'd like to see. You'd like a spreadsheet view that has all of the accounts. The old ones and the new ones. You'd like to see those that are unchanged line up so they're side by side. So if account 1010 Cash has $1000 before and after, they just line up.
You'd like to see empty space in the original chart of accounts if the new accounts don't match the old ones. You'd like to see empty spaces on the new side if an old account was deleted.
You'll see in the images below how Row by Row differs from Cell by Cell. Also, you'll see how to adjust the number of Key Columns.
Cell by cell comparison
Here's row by row and merge with the key column on the account number
Notice how the two sides are lined up. Even account 1040 lines up even though the account name is different. On the left, you'll see a 1050 that is no longer on the right. It looks like account 1050 was renumbered as 1090. Now it's easy to visualize.
Here's row by row and merge with two key columns - account number and text
You can assign the first two columns as key columns for comparison. When 2 key columns are selected, it means that only rows will line up if those two key columns are the same on both rows. This is VERY hard to do in Excel. Maybe impossible. It is so useful. Notice that the way it's done is that the two key accounts are kind of merged and sorted. You can use the search feature and sorting feature, but you can always come back to the SortID on the far left (see orange arrow) to get back to the original sorted version.
Finally, you'll find the same image shown above, but only differences
This is the same as above, but it only shows the differences. If there are hundreds or even thousands of accounts, and you only need to see the differences, here you go.