Home |
About Us |
Back
How to Generate the Proper Report Format Using EXCEL Spreadsheet
Many stores are already using Excel and we will customize our scripts to work with your spreadsheet.
The instructions herein discuss Excel formats for DAILY and MULTIPLE DAY WorkBooks. There are also
instructions for stores that use multiple WorkSheets in one WorkBook.
However, for those stores that are just starting to use Excel we would like you to consider using
the following format designed to record every transaction one per row. Excel has a column filter function
which should make it possible to generate monthly vendor reports:
MULTIPLE DAY FORMATS
--------------------
GLAM
Date,Vendor,Item #,Qty, Description,Ticket Price,Sales Price,extra columns
1/31/13,B-14,G136,1,Set of 6 Ramekin,$8.00,$8.00,$0.74,Cash,
1/31/13,SQ,2563,1,Set of 6 Name Place Holders,$5.00,$5.00,$0.42,Cash,
1/31/13,B-14,G126,1,Guest Book,$6.00,$6.00,$0.56,Cash,
1/31/13,SQ,1205,1,Porcelain Teapot,$20.00,$20.00,$1.85,Cash,
1/31/13,BUR,,1,Teapot,$19.00,$19.00,$1.76,Cash,
2/1/13,B-40,,1,Decorative Wall Hanging,$8.95,$8.95,$0.83,Cash,
2/1/13,B-12,,1,Pink Side Table,$17.00,$17.00,$1.57,Cash,
2/1/13,B-90,,2,Teapot Shelves,$50.00,$45.00,$4.17,Check,
2/1/13,B-6,,1,Red Hearts Earrings,$1.00,$1.00,$0.09,Cash,
2/1/13,B-14,G107,1,,$2.00,$2.00,$0.19,Cash,
The minimum fields we need and in sequence are:
Date (mm/dd/yy)
Vendor (dealer or booth number)
Item # (ie stock number)
QTY
Description
Ticket Price
Sales Price
Ticket and Sales Price are for all quantities. In other words, a Ticket price of $50 for a qty
of 2 means each item was marked $25.
The extra columns shown above are optional and not used. You may add additional
columns and they will be ignored during the upload.
Method is to export your report in eith CSV or TAB DELIMITED format. Save it as "sales.txt" in your
Documents folder and launch the shortcut to upload the report.
To use the format above the first line contains the word "GLAM" and be sure
your Store Account is set for EXCEL-TMA.
---
Other stores may use a different format. For example, this is the TMA format:
TMA
Date,Vendor,Item #,Description,Ticket Price,Sales Price,Tax,Payment,
1/31/13,B-14,G136,Set of 6 Ramekin,$8.00,$8.00,$0.74,Cash,
1/31/13,SQ,2563,Set of 6 Name Place Holders,$5.00,$5.00,$0.42,Cash,
1/31/13,B-14,G126,Guest Book,$6.00,$6.00,$0.56,Cash,
1/31/13,SQ,1205,Porcelain Teapot,$20.00,$20.00,$1.85,Cash,
1/31/13,BUR,,Teapot,$19.00,$19.00,$1.76,Cash,
2/1/13,B-40,,Decorative Wall Hanging,$8.95,$8.95,$0.83,Cash,
2/1/13,B-12,,Pink Side Table,$17.00,$17.00,$1.57,Cash,
2/1/13,B-90,,2 Teapot Shelves,$50.00,$45.00,$4.17,Check,
2/1/13,B-6,,Red Hearts Earrings,$1.00,$1.00,$0.09,Cash,
2/1/13,B-14,G107,,$2.00,$2.00,$0.19,Cash,
The minimum fields we need and in sequence are:
Date (mm/dd/yy)
Vendor (dealer or booth number)
Item # (ie stock number)
Description
Ticket Price
Sales Price
The qty is assumed to be one for each line. The first line of the spreadsheet should be
marked "TMA" and your Store Account needs to be set for EXCEL-TMA.
The extra columns shown above (ie tax and Payment) are optional and not used. You may add additional
columns and they will be ignored during the upload.
Method is to export your report in eith CSV or TAB DELIMITED format. Save it as "sales.txt" in your
Documents folder and launch the shortcut to upload the report.
---
This is the LOGCABIN format:
LOGCABIN,,,,,,,,,,
Date,Description,Quantity,Vendor,Net Sale,Tax,Total Sale,Dealer Share,Type,CC Fee,Pay Vendor
,,,,,,,,,,
3/10/13,Bunny,1,1,1.00,$0.05,$1.05,$0.11,,,$0.90
3/10/13,Pillow,1,1,7.00,$0.35,$7.35,$0.74,,,$6.27
3/11/13,,1,1,1.25,$0.06,$1.31,$0.13,,,$1.12
3/23/13,basket,1,1,15.00,$0.75,$15.75,$1.58,,,$13.43
3/23/13,bracelet,2,1,10.00,$0.50,$10.50,$1.05,,,$8.95
3/27/13,dolphin figurine,1,1,1.00,$0.05,$1.05,$0.11,,,$0.90
,,,,,,,,,,
3/9/13,purse,1,2,7.00,$0.35,$7.35,$0.74,,,$6.27
3/10/13,p(?),1,2,4.00,$0.20,$4.20,$0.42,,,$3.58
The minimum fields we need and in sequence are:
Date (mm/dd/yy)
Description
Quantity
Vendor (dealer or booth number)
Net Sale
Stock number is missing and so is Ticket Price. The Net total is essentially the Ticket and
Sale Prices. Notice blank lines are acceptable ",,,,,,,,,,,,,".
The first line of the spreadsheet should be marked "LOGCABIN".
The extra columns shown above (everything past Net Sale) are optional and not used. You may add
additional columns and they will be ignored during the upload.
Method is to export your report in eith CSV or TAB DELIMITED format. Save it as "sales.txt" in your
Documents folder and launch the shortcut to upload the report.
(use EXCEL-TMA)
----------------
DAILY FORMATS
-------------
Coming...
(use EXCEL-DAILY)
------------------------------------------------------------------------------------------------
Other formats are available. Please check with tech Support at Mall-Central.com.
------------------------------------------------------------------------------------------------
MULTIPLE SHEETS
---------------
Some stores may have a spreadsheet with multiple WorkSheets or Tabs. When an export is made only the
active WorkSheet or Tab is exported. To export all WorkSheets you must create a Macro. The Macro will
export all WorkSheets to a specified folder and name each file with the same name as the WorkSheet
followed by the suffix .csv . CSV is a valid text file suffix and is used to identify comma-separated-values.
However, we are actually saving the WorkSheet in a tab delimited format and use the CSV
suffix for convenience.
The Helper File, SALES_UPLOAD.BAT, is then modified to combine all the .csv files into one file,
sales.txt, and then do the upload.
The following instructions should help the user create the Macro and modify the Helper File.
FIRST, READ THESE INSTRUCTIONS ONCE. SECOND, MAKE A BACKUP COPY OF YOUR EXCEL FILE.
These instructions will show you how I figured out how to create a Macro in Excel and add button to
your Worksheet.
I got these instructions from a friend:
First you need to go into your Excel options and turn on the developer options:
Then use the 'Record Macro' option to record the process you use to export your sheets. Once you have
your macro saved you can edit it to your liking. Then you can add a button to your sheet using the insert
option under the developer tab and assign your new macro to it.
Using the record macro option I was able to extract some VBA code. Googling I found the rest of the answers:
HERE IT IS:
Bottom line is create a Macro. I named it MallCentralExport. Do this by going to the Developer ribbon
or menu. Click on Macros. Enter the Macro Name and click on Create. A new window opens where you can copy
and paste the following:
Sub MallCentralExport()
'
' MallCentralExport Macro
' Export all sheets to tab delimited text files with .csv extensions
'
'
Dim i As Integer
For i = 2 To Worksheets.Count
Sheets(i).Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\us6133\Documents\MC_yourstoreid\" & ActiveSheet.Name & ".csv", FileFormat:=xlText, _
CreateBackup:=False
Next i
End Sub
This macro will step thru all the sheets starting with the second sheet.
I was able to place a button on the first sheet.
You need to save the WorkBook as a Macro Enable Workbook or .xlsm file.
You also need to create a folder in your Documents folder called MC_yourstoreid and be sure it matches the path in the macro.
For example 'us6133' does not apply to you. On your computer I am not sure what your pathname will be. If this stumps you
Then I recommend we do a TeamViewer session so I can do this for you. Download and install TeamViewer for 'personal use'
from www.teamviewer.com and run it. You will see your ID number and password. You will need to give those to me when I call.
After you run the macro I think it converts the WorkBook to a text file so you don't want to save it after an export.
You may want to confirm this but be careful about having a backup.
Download all the Helper Files (from your Mall-Central.com Store Page) into the MC_yourstoreid folder.
Modify SALES_UPLOAD.BAT file to add these two lines to the beginning:
copy *.csv sales.txt
del *.csv
Be careful about the syntax and spaces.
(I can do this during a teamviewer session, too)
This will concatenate all the .csv files into one sales.txt file and delete the .csv files
The third line in the SALES_UPLOAD.BAT file will upload sales.txt file to Mall-Central.com.
Create a shortcut of SALES_UPLOAD.BAT and place it on your desktop for your convenience.
Launch the shortcut on your desktop for SALES_UPLOAD.BAT.
That's it.
Every night before you close your Excel file do the following:
Save your file.
Click on the Export button you created on the first sheet of your Excel file
Close the file without saving.
Launch the shortcut.
Check your on-line sales.
Kick the cat out and turn out the lights.
Whew!
You do have to make some changes to your Excel spreadsheet but these are very minor:
You need to add JSH to the first row in the first column of each page.
You need to add the Dealer Number to each sheet. I think you have the Dealer Number as the NAME of
each sheet but it also need to be on a row. For example, under the month and year add Vendor J1.
Another is you are not to change your spreadsheet by changing the order of the columns or
inserting columns. You may add columns to the right but that is it. All columns must be in the same order.
I will always be looking for a valid date in column C (ie m/d/yyyy). You may add rows but if the
row has a date in column C and a value greater than zero in columns D or E then I will see it as a sold item.
Also, quantity will be assumed 1 for each row. If two items are sold they should be listed on separate rows
Or the quantity be placed in the description. I will be reporting the sold price as that shown in col D or E.
No discounts calculations. No quantity calculations. Any other information will not be displayed on-line.
Support team at Mall-Central.com may have to write a special parser to be able to read your specific
format before the data appears better.
This format does not specify qty. The first column is stock number although none is shown here. Price sold
is found in one of two columns. Mark file with JSH
JSH
"JANUARY, 2016"
VENDOR 10
ITEM NUMBER Description Date Sold Credit/Debit Cash/Check CARD FEES TAX EXEMPT CUSTOMER
ENAMEL PIE DISH 1/1/2016 $7.00
SNOWBALL 1/2/2016 $1.50
SNOWBALL 1/2/2016 $1.50
FABRIC BALL 1/2/2016 $2.00
SMALL BERRY & TIN STAR RING 1/2/2016 $3.00
HAND DECORATED BELL ORNAMENT 1/17/2016 $4.00
CHENILLE SNOWBALL 1/23/2016 $1.50
$15.00 $67.50
$82.50 $0.45
$82.05
"JANUARY, 2016"
VENDOR 101
ITEM NUMBER Description Date Sold Credit/Debit Cash/Check CARD FEES TAX EXEMPT CUSTOMER
TRUMPET 1/2/2016 $16.50
BRASS STICK 1/6/2016 $32.30
(use EXCEL-TMA)
----------------
Back to Store Page instructions