Its main function is to parse the sales items into a flat file and then create a summary table showing the method of payment used to purchase each item. I couldn't think of why one needs this. But it seems anIndianaGirl was asked for something like this. They want to see if certain items are paid for by CC more often than cash. (to raise price to cover CC charges maybe?) So maybe there are others who need to know too? What challenged me was getting the multi-pay transaction information added to each line item. The trans number and dates were not too bad.
Any ways, this will help answer the burning question of, "How do people pay for my Golf Balls?" "What about for Shoe Horns?"
This app will allow you to find the payment type(s) used to pay for each item sold. ie by Cash, Check, Credit, Debit, Gift Card, or User Defined.
If you accept more than one form of payment AND ALSO allow split-payments (customer can pay part in Cash and part in Credit Card) then this app is Perfect for you! Without this app, the answers are not easy to come by with just the CSV file created by the REC2CSV.
If you accept more than one form of payment, but only allow one method of payment per transaction then this is kind of easy to figure without this app, but this app sure makes the job easy. You may have some interest in this app.
If you only accept one form of payment (ex.'Cash') in your register, then this program is probably not for you. The answer to the burning question is easy to figure out without this app. Although if you need it, the app will give you the answer.
WHAT ARE THE REQUIREMENTS:
1. You need to be running at least DHPOS version 7.05k. I think that is the first version that included a well behaved REC2CSV.EXE.
But version 7.1b or greater is prefered. This is the one I used to create the app. Before 7.1b there is a very minor bug in REC2CSV (payment type for Returns) but my app handles it. Take a moment to read about REC2CSV.EXE and what it does. Dale reveals all the secrets here >About REC2CSV.EXE
2. Microsoft Excel 2002 or greater. Note that 'greater' might not be so great! I wrote this in MS Excel 2002 and it works. Knowing Microsoft, this may work upto Excel 2007 which is when many changes were made and shockingly droped some older supported stuff. I don't know for sure. I have no way to test any of this because I don't have the newer versions. BUT try it, you and I may be surprised that it works fine. So YMMV. Also you must have macros enabled or at least set Excel's security level to Medium so it prompts you to 'run macros or not'.
3. You need a CSV file created by the REC2CSV.EXE program. Before using this program you must first run DHPOS REC2CSV.EXE to create a .CSV file. This program will do almost nothing if the expected .CSV file is not created FIRST.
4. This version of the app (ver. 0.00a) expects the default names created by DHPOS in the Receipt Language of posconfg. If you change the headings descriptions (specifically the TENDERED headings) then this will not be able to find the payment methods and will produce a flat data set with all pay methods showing zero. Vendor and Stock should still work OK along with the Unique Stock number sheet. I am working on the fix for this in the next version. If you didn't change any of the Receipt Language in posconfg then you are good to go!
Click Here to Download the app: RecCSV_Parse.xls App
How does it Work?
1.Open the Rec2CSv_Parse.xls (macros must be enabled)
2.Click on the 'Start' button on the worksheet.
3.Enter the path and name of your register. (example: "C:\POS\MYREGIST.POS")
4.Enter the path and name of your registers CSV file created by REC2CSV.EXE (example: "C:\POS\MYREGIST.CSV")
5.Click the OK button.
If you leave all options checked, the app will create the following.
1. Summary Pivot Table "Stock by Payment Method(s)". The main purpose of this app.
If your data contains multi-pay transactions the Summary Report will show a count under each method used to pay for the item. The Total column at the right end of the summary table reflects the actual count of sold pieces. It will only summarise the pay methods it finds in the REC file. So even though there are 6 different pay methods in DHPOS, the app will only create columns for the methods found in the REC data.
ie if only cash and credit are found then that is the only 2 columns created. Again, this is based on what is in the REC file not what your POS is setup to accept. If your POS is configured to accept all 6 payment types but only 3 are actually used in the course of business found in the REC file then only the 3 will be listed and summarised. (no need for empty columns or just zero filled, right!?).
One of the nice features if you don't have split payments is that on the summary pivot table if you double click any items total it will open another worksheet with the details of that row item. If you have split payments it does the same but the info is not as informative. So
YMMV.
Sample of Multi-Pay (split payment type transactions).
Sample of Single Pay type transactions.
NOTE: If you double-click a Grand Total it will open a sheet with the details of all transactions related to that stock numbers information.
This app will also include:
2. Worksheet with the flat REC data.
Each item has its transaction#, sales date, payment method(s), tax Rate ID, pieces sold, $, vendor#, vendor name. etc.
Note that column 'G' ("PayBy") is the combined payment methods used for the transaction.
Payment methods are numbered: 1=CASH, 2=CHECK, 3=CREDIT, 4=DEBIT, 5=GIFT CARD, 6=USER DEFINED (matches the order as listed in POS payment methods. clever, eh) Example if customer paid cash it will show 1. If split payment paid by cash and Credit it will show 13. ( 1 for cash and 3 for Credit ) IF you have Split-Payment transactions. You will see payment types parsed out to the last columns by using a formula this is used to creat the pivot table summary report. IF you have Single Pay transactions, you will see the payment type in a single column at the right end. This is is used to creat the pivot table summary report as shown above.
Sample of flat transaction data from the REC CSV file with Single payment type for each transaction.
Sample of flat transaction data from the REC CSV file with Multi-payment types for each transaction.
3. Vendor worksheet. This is your vendor list found in your POS. (used as a lookup for each item's vendor Name)
You can use this to do your own data mining. (There is no export Vendor list 'feature' in DHPOS, so this may be the selling point for you to use this app). This is refreshed each time you run it. So new vendors added to the POS will import the next time you run the app.
4. Stock Table worksheet. This is your stock table found in your POS.(used as a lookup for each item's vendor ID)
This is refreshed each time you run it. So new stock table changes in the POS will import the next time you run the app.
5. Unique Stock Number worksheet. This contains list of unique stock numbers found in the REC data.
If you sold one or a thousand of an item, that stock number will be in this list. You can use to do your own data mining.
Creating the Summary Pivot table is the most time consuming. If you don't need it but want the other worksheets just un-check the 'Create Summary' check box. The app will then create the other worksheets as specified.
I have tested this on an aging Celeron 2.4Ghz w/1GB RAM. Running Excel 2002, and DHPOS 7.1b.
This app created the results in about one and half minutes. Most of it was creating the Summary Pivot table.
My register's stock table has 26,000 items and 254 Vendors.
The Rec .CSV file I exported out of my register contained 44,900 lines.
The Rec file had 30,000 line items(stock#s) sold, and 12,600 of them were unique stock numbers.
The Vendor and Stock table is done in a flash. The flattening of REC data is about a flash and a half.
The progress bar updates the user as it runs. When it says 'Please wait...' please do. Much of what is going on is using Excels native features which avoids the need for relatively slow loop code. The down side is there are no flashy 'count down' to completion. So when it says 'Wait', do so. Trust me its working very hard and will give you a message box saying its done, right about the time its done.
IMPORTANT!!!!!
This is not a finished product.
It is an experimental project for testing at best.
The Most important thing to know is I have no idea what I am doing and therefore I am not resposible for any results.
Feed back is appreciated and may encourage further developement and added features. (like flatening all transactions like Register Open, Register Close, No Sales, Cash In, Cash Out).
If you have comments/ideas/questions post them here in the forum is prefered but you
can send email to: brucef2112 AT AllOtherLoser DOT CatOnMat
