Press release:
www.prweb.com/releases/finding_needles_in_haystacks_finally_simplified_with_new_self_serve_from_crusherrors_com/prweb15733580.htm
Video:
https://youtu.be/oVMNdd_MJ7g
We developed CrushErrors.com to find needles in "digital" haystacks. Similar calculations are impossible in Microsoft Excel and Google's OpenRefine. Today, we introduce the easy-to-use Self-Serve version at https://CrushErrors.com/pay-as-you-go.
Press release: www.prweb.com/releases/finding_needles_in_haystacks_finally_simplified_with_new_self_serve_from_crusherrors_com/prweb15733580.htm Video: https://youtu.be/oVMNdd_MJ7g
I’ve been struggling with CrushErrors.com messaging for years. A VC asked me for a 2 minute pitch. I only wanted to demo my amazing software. It does so much. It’s revolutionary. I couldn’t reduce it to a pitch. That didn’t go over too well with the VC.
I believed that the domain name, CrushErrors.com, was already broad and explanatory. While true, it's too hard to visualize. And what, specifically, does crushing errors really mean? I also tried to say that my software replaced the very old-fashioned “stare and compare.” While true, it doesn’t convey the full nature of the software either. It's visual, so I tried it anyway. I had many other ideas along the path, but nothing was perfect. Meanwhile, I continuously had to add new functionality to the software. Why? Because it is shockingly hard to find needles in haystacks. And all of the new power forced me to add more artificial intelligence to reduce the complexity of implementing the new power. Then I tried more messaging ideas. I tried to get specific. I explained how the software can be used. It reconciles. It finds fraud. It finds ghost accounts. It finds every kind of human error in data. While all true, it's too much detail. There is no emotional reaction. No visual. I recently explained the software to an industry professional. For some subconscious reason, this time I blurted out “WE FIND NEEDLES IN HAYSTACKS.” It felt perfect. Why do I like it? It’s easy to visualize. It conveys the challenge of finding needles. It has proven itself as both powerful and memorable, as it has been an expression since 1532. Bottom line is that “WE FIND NEEDLES IN HAYSTACKS" is my new message. *The asterisk. As I'm a stickler for precision, I feel compelled to reveal that I should asterisk the “WE FIND NEEDLES ...” message. In most cases, we actually scan TWO haystacks. We compare them using many AI algorithms. The difference reveals the needles. **A second asterisk. Sometimes it is not perfectly clear that hay is really hay. Sometimes hay is absolutely, definitively hay. Sometimes it is not 100% certain, so that is flagged. Users evaluate flagged items and apply human insight to confirm the AI. Who is the "WE" in “WE FIND ...” ? The WE could be (A) me and my experts, (B) the users, or (C) some combination. We could teach users how to find their own needles or it can be full outsourced. Next Steps Visit CrushErrors.com to schedule a demo. Give us the opportunity to prove ourselves with some of your sample data. Security All data and calculations remain local. Nothing is transferred over the Internet. Enjoy Although CrushErrors is not as fun as watching the following video, please watch it to see how it may have the best messaging of all. Even though a user told me that my software is a little bit like playing a video game, it doesn't come close (https://goo.gl/QVWBCe): Thanks for reading, Doug Ranking the Quality of Reconciling ItemsWhy is this an issue?This article addresses a problem that accountants and data users frequently experience. But, honestly, they don't think about it exactly in the terms presented below, largely because it is very hard to solve manually and even very hard for Excel. There is a solution that makes it much easier that is presented at the end. If you have two sets of data that need to be reconciled, like a bank reconciliation or even more complex data, there are perfect matches, less than perfect matches, and no matches. First consider a super simplistic example. Let's say each side only has three columns: check number, date, and amount. For this example, they correspond to the letters A, B, and C. Ideally you would want to match on all 3, so ABC on one side exactly equals ABC on the other side. However, sometimes the check numbers could be different, but the dates and amounts could match. Or Let's say the check numbers match, but the dates don't. So you can see already there are combinations of values. So here are the combinations: ABC, AC, and BC. That makes perfect sense if there is one amount that corresponds to the amount on the other side, but what if someone forgot to enter all of the check numbers on April 15. So now you'd want to compare the SUM of the April 15 checks on one side to the sum on the other side (refer to B combo below). Similarly, let's say a user enters multiple amounts with the same check number to reflect that there are items purchased, like a portion allocated to food, another to entertainment, another to air transport. But, in the bank, there is only one check. So the sum of the checks could match (refer to A combo below). All of the combinations are below (there are 8 (2^3), but that math is not the focus of this piece): Finding results in ExcelUnfortunately, without some very intense VBA programming, there is no way to automatically cycle through every combination. Below, we're going to go through 2 combinations. Instead of just the 3 fields shown in Table 1, we're going to have 5 fields (ABCDH) that define the perfect match and 1 date (C) field that represents the imperfect matches. There are 32 different combinations of 5 columns, but after you see the complexity to any single combination, you'll see why we're only looking at 2. Looking at Table 2 - Sample Bank DataWe need to create two helper columns for this data. The first column creates an Amount column which is equal to the Debit column minus the Credit column. This is referred to as column H. The second helper column concatenates all of the fields in the main columns. It uses Excel's new CONCAT function. Because it's in a table, it is simply: =CONCAT([@[Doc ID]],[@Type],[@[Date Posted]],[@Name],[@Amount]) There is one more column in Table 2, in column J, that describes the quality of the match. We'll get back to this.
Looking at Table 3 - Internal DataWe need to create one helper column. It is column F. It concatenates all of the fields in the main columns. It is similar to column I in Table 2: =CONCAT(internal[@[Number]:[Amount]])
Quality of MatchesSo now all of the data is in place and the final connection needs to be made. We need to put in the "Quality of Match" data into Tables 2 and 3. We need to say whether it's a PERFECT match, meaning all key fields, or just Date matches. Or no match. Remember that theoretically there could be other combinations, but that's too much manual work. Below are the formulas for Column J in Table 2 and Column G in Table 3. It first looks for perfect matches. If it doesn't find, it looks for Date matches. If it still doesn't find, it's a no match. The formulas uses the new IFS function. Here is column J in Table 2: =IFERROR( IFS( SUMIF(internal[Most fields],[@[Most fields]],internal[Amount]) = SUMIF([Most fields],[@[Most fields]],[Amount]), "PERFECT", SUMIF(internal[Date],[@[Date Posted]],internal[Amount]) = SUMIF([Date Posted],[@[Date Posted]],[Amount]), "JUST DATES" ) ,"no match") Here is column G in Table 3. The formula is almost identical to the one in Table 2. The only difference is that in Internal data, the date field is called "Date", unlike the Bank data in Table 2, which is called, "Date Posted" =IFERROR( IFS( SUMIF(bank[Most fields],[@[Most fields]],bank[Amount]) = SUMIF([Most fields],[@[Most fields]],[Amount]), "PERFECT", SUMIF(bank[Date Posted],[@Date],bank[Amount]) = SUMIF([Date],[@Date],[Amount]), "JUST DATES" ) ,"no match") How do these formulas work
Drawbacks
Are you kidding me? There must be a better way! There is!Doug Schiller created CrushErrors.com to solve this problem, along with many others. Here are the steps with CrushErrors.com.
CrushErrors.com found, for example, the match for the payment of $1,022.00. Our Excel model did not find that match because the date was different. Crush found it because the amounts were identical. I recommend that users with any kind of reconcilation problem consider using CrushErrors.com. It solves very basic problems experienced by many people every day. ScreenshotsPower Crushing has been a challenge to explain. I'm sorry. It's actually simple in concept, but I've been struggling. A few weeks ago I thought of a way to present the results visually, so I'm going to give the explanation another shot. I know more can be done visually, but this is such an improvement that it deserves an immediate blog post. I can say without any hesitation, that Power Crushing is next to impossible in Excel. In brief, it is a way to look for thousands of combinations of columns to find the best matches. And it does it in an order so that it finds the best matches first. The matches progressively are of poorer quality. Why is this needed? Let's say you have two sets of data from two different sources and you want to see what matches, then you need Power Crushing. It's not needed if your data is pretty much perfect, like let's say, each row has a unique ID and they just need to be matched up. But most Crush users do not have perfect data. So let's say you have bank data with debit, credit, transaction date, transaction id, and description columns. Let's say you have similar data in your internal records. Let's say it's also debit, credit, date, and an id column. So the names of the columns are different. You basically load the data into Crush. Use drag and drop from your desktop. (The data never goes to our server. It always stays local so there should be zero privacy or security concerns.) Then press the AI button. This organizes all of the data. It does the debit minus credit math. It knows that the bank side should be credit minus debit (to make the signs match). All of above could be done in less than a minute. It's pretty much fully automated. Then press the Power Crush button. Crush first tries to match all of the columns. The date, id's, text, and amounts. You don't have to say column A should match with column J. It just knows what to do. A real world example is shown below. All of the data is available in the case studies. Also, a complete report is shown in the Cash Audit report under the Features tab above. Image # 1 below shows that Crush is looking at 55 combinations of columns. All of the math is solved in 2 seconds. There could be tens of thousands of combination. Sometimes it could take a minute to do all of the calculations. Image # 1 shows that 663 rows on both sides matched. The remaining rows, 16 on the left and 29 on the right need to be reconciled manually. There are tools that make it easier, but fundamentally, it's manual. Image #2 below sows that 337 rows are found that match on Date, Check Number, and Amount. Then there are 113 that match on only Check Number and Amount. Then there are 208 that match on Date and Amount. Then there are 5 that only match on the amount. Notice in Image # 1 there are two extra fields on the left: Num and Type. Crush tried to match those, but it didn't match. In some cases, when, for example, there are two dates on one side and one on the right, then there could be matches on one date and not the other (example: say order date and invoice date). Image #2 below shows the letter "X" to indicate which columns match in which groups. In Group 001 there are 337 matches and there is an X in each column. The dark blue headers indicate that it is a column on the left that was part of the original data that was supplied by the user. The brighter blue indicate columns calculated by Crush. (Same for Red on Right side.) The user can drill down to the specific rows by clicking on the detail button. Advanced IdeasThe above data and examples are one-to-one comparisons. But Power Crush can also do one-to-many. Excel would struggle to do either. Really more than struggle. More like impossible. (Excel could do some of the work if the user knows exactly what columns to match up, but it could not be automated.) Below is an example of one-to-many. Let's say you have two different reports for invoices to customers and payments from customers. And you want to compare these two reports. Let's say they are the same customers and the same periods, but for some reason, prepared by two different people. So they will be slightly different. One reason why this could happen is that there could be a new employee and they want to recreate the old data to start rolling it forwards. There could be many reasons. In the data below, the total for User 1 is $600 and the total for User 2 is $0. The goal is to figure out why they are different. The method is to find the perfect matches, then progressively look for less perfect matches, and finally to figure out what is remaining, which would identify the problematic transactions.
Merged Data with trying to match maximum Fields, |
Customer |
Type |
Date |
Num |
Amount |
Note |
ABC |
Invoice |
1/1 |
1001 |
$100 |
All FIVE fields match |
ABC |
Invoice |
1/2 |
1002 |
$200 |
All FIVE fields match |
ABC |
Invoice |
1/3 |
1003 |
$300 |
All FIVE fields match |
ABC |
Invoice |
1/4 |
1004 |
$400 |
All FIVE fields match |
ABC |
Invoice |
1/5 |
1005 |
$500 |
All FIVE fields match |
ABC |
Payment |
305 |
-$500 |
Date not match, but matches on FOUR fields |
|
ABC |
Payment |
-$1000 |
Sum remaining TWO fields. Both equal $1000. |
||
ABC |
Invoice |
1/6 |
1006 |
$600 |
ONLY USER 1 --- NOT USER 2 - THIS IS PURPOSE OF THIS EXERCISE - TO IDENTIFY WHAT DOES NOT MATCH |
I've added so many new features since the last time I blogged, but this latest one is really insane. Now users only have to upload their data and the AI engine will figure out what the user is trying to do. Crush will build an App that matches the specific needs. It does not work 100% of the time, but it will work with the most common problems, including bank reconciliations and comparison of trial balances, etc. There's also a button to scan the App store to see if there are compatible Apps that match the uploaded data. Crush just got MUCH easier and more powerful. Other features added since the last post include a complete Matching system. Users can match transactions, star them (1 is a poor quality match, and 5 stars means it's perfect), and notate them, and report on the system. There's also a file save and file open feature. Users can now save their work to continue at another location. Or delegate work to others. Or just record the history of the Crush session. |
Hi. I'm Doug Schiller. I designed CrushErrors.com to save time and end my own frustration reconciling accounts. I absolutely stand behind this product. Let's set up a demo. (c)2019 |