Aylık Satış Raporunu Tek Tuşa İndirmek: Excel VBAOne-Click Monthly Sales Report with Excel VBA

The monthly report, by hand

In most teams the monthly sales report is built with the same steps: open the raw export, filter out cancellations and shipping lines, use pivot tables for revenue, orders and customers, compare with last month, refresh the charts and send a PDF. The steps never change, yet doing them by hand takes time and a forgotten filter silently changes the numbers.

For this case study I wrote an Excel VBA macro that turns that process into one click. The data is 541,910 rows of an online retailer's sales from December 2010 to December 2011 (UCI Online Retail II). The macro reads the source file, classifies every row, builds the report for the chosen month and exports it to PDF; optionally it produces a separate PDF for each of the 13 months in the data.

What the macro does

1. Settings sheet: source file, sheet name, report month and PDF folder in one place, editable without opening the code.
2. Single read: the source file opens read-only, the whole sheet is loaded into memory (an array) with one statement and the file closes. Using an array instead of reading cell by cell brings the work down to seconds.
3. Classification: every row is labelled as a sale, a return, a service code or invalid.
4. Summary in one pass: gross sales, returns, distinct orders and customers, a daily series, and product and country breakdowns are accumulated per month in dictionaries (Scripting.Dictionary).
5. Report sheet: KPI cards (with change vs the previous month), a daily sales chart, the top 10 products and the country split are built from scratch.
6. PDF and log: the sheet is saved as a one-page A4 PDF; each run is written to a Log sheet with row counts and run time.

' Kaynak dosyayı salt okunur aç, tüm sayfayı tek seferde diziye al, kapat
Set wb = Workbooks.Open(Filename:=src, ReadOnly:=True, UpdateLinks:=False)
arr = wb.Worksheets(sheetName).UsedRange.Value
wb.Close SaveChanges:=False

One month's report takes 12.9 seconds including opening the source file, of which about 5 seconds is opening the 45 MB file. Because the TumAylar macro reads the data only once, all 13 reports and PDFs are done in 26.8 seconds.

Which rows count as sales?

Before speed, the report's correctness depends on this decision. Besides sales, the raw data contains cancellation invoices (starting with C), non-product lines such as postage and bank charges, and adjustment rows with zero quantity or price. The macro separates them with these rules:

For r = 2 To UBound(arr, 1)
    If IsServiceCode(code) Then
        nSvc = nSvc + 1                         ' kargo, banka masrafı...
    ElseIf Left$(inv, 1) = "C" Then
        AddNum mIade, mk, -amt                  ' iptal faturası = iade
    ElseIf qty <= 0 Or price <= 0 Then
        nBad = nBad + 1
    Else
        AddNum mBrut, mk, amt                   ' ay bazında brüt satış
        Sub2(mInv, mk).Item(inv) = 1            ' tekil sipariş
        Sub2(mCust, mk).Item(cust) = 1          ' tekil müşteri
        AddNum Sub2(mProd, mk), code, amt       ' ürün kırılımı
    End If
Next r

Net sales = gross sales − returns in the same month. Orders and customers are counted as distinct values; dozens of lines with the same invoice number are one order.

Output: a one-page report

Below is the PDF the macro produced for November 2011. Net sales £1,432,735, up 34.8% on the previous month; 2,751 orders. Average basket fell 4.0%: before Christmas, order count grows faster than revenue.

Are the numbers right?

Being fast is not enough; an automation's numbers have to be trustworthy. I wrote the same rules independently in Python (pandas), read the six KPIs (net, gross, returns, orders, customers, basket) from the text of the 13 PDFs the macro produced, and compared them. All 78 values matched exactly; so did the row counts per class.

The macro calculates; a person interprets

In the December 2011 report, returns look about seven times higher than the previous month (£174,127). The product table shows why: on the morning of 9 December one customer ordered 80,995 units of a single product (£168,470) and the order was cancelled 12 minutes later. The same happened in January 2011: a 74,215-unit order worth £77,184 cancelled within 16 minutes.

Net sales are right here, because the sale and the return cancel out. But gross sales, the return rate and the top-product list are misleading: in December this one product makes up 27.4% of gross sales. That is why gross and net sit side by side in the report; a natural next step is a check that flags large orders opened and cancelled on the same day.

Limitations

• The macro uses Scripting.Dictionary; it runs in Excel for Windows, not in Excel for Mac.
• An Excel sheet is limited to 1,048,576 rows; for larger data Power Query or a database is a better fit.
• The rules are written for this dataset (service codes, C invoices). For another source the IsServiceCode list needs updating.
• Timings were measured on a single computer; file-open time depends on the disk and the file size.

Files

To try the macro on your own data: RaporModulu.bas (the VBA module) and Rapor_Otomasyonu.xlsx (the settings sheet). In Excel, add the module with Alt+F11 → File → Import File and save the file as .xlsm. Data: UCI Online Retail II (CC BY 4.0).