Tally Stock Summary to Excel: Batch and Expiry Export

The problem with a plain stock summary

You open Stock Summary in TallyPrime, press export, and get a neat sheet: item names, closing quantity, rate, value. Then you try to answer a simple question — what expires in the next sixty days? — and the sheet can't tell you. There's no batch column and no expiry column. Just one line per item with everything rolled together.

That's not a fault in the export. The default Stock Summary is built for valuation, so it adds every batch of an item into one total. Your expiry data is still in Tally. It just sits one level down, and you have to ask for it on purpose.

This post covers where batch and expiry live in TallyPrime, how to get them into Excel or CSV, which columns an expiry check actually needs, and the export mistakes that quietly wreck the file.

Check the item setup first

A batch-wise export can only show what Tally was told to record. Expiry dates show up only for stock items where batches are switched on and expiry dates are enabled in the item master (the "Maintain in batches" and "Use expiry dates" options). If those were never turned on, or were turned on halfway through the year, you'll get batch lines with blank expiry cells.

So before you export anything, open a few of your fastest-moving items and check those settings. If half your catalogue was created without them, an export won't fix that. The gap is in how stock was received. We go through those limits in more detail in does Tally track expiry dates.

Where batch and expiry show up in TallyPrime

There are two practical routes. Menu names can move a little between TallyPrime releases, so go by what each report shows, not the exact path.

• Stock Summary with batch detail turned on. Open Stock Summary, then use the configure option (F12) to show batch-wise details. Each item then expands into one line per batch, and you can switch on the expiry and manufacturing date columns.
• The batch-wise report under Statements of Inventory. This lists batches directly, with batch number, closing quantity and expiry. It's often the cleaner one to export because it starts at batch level.

Whichever you use, set the period to end today (or whatever date you want stock "as on"), and pick whether you want every godown or just one. If you run more than one godown, include the godown column. The same batch can sit in two places, and you'll want to know which shelf to walk to.

Exporting to Excel or CSV

With the report on screen, use Export (Ctrl+E in TallyPrime). You'll get a choice of formats. Pick Excel (spreadsheet) if a person will read it, or ASCII / comma-delimited if the file is going into a tool or template.

Before you confirm, check the export configuration. It usually defaults to "as displayed", so whatever's on screen is what you get. If the screen is collapsed to group level, the file will be too. Expand to item and batch level first, then export.

Save the file with the date in its name. A month from now, "stock.xlsx" and "stock (2).xlsx" won't mean anything to you.

The columns an expiry check needs

An expiry check asks three things: what's expiring, how much money is sitting in it, and will it sell in time. That maps to these columns:

• Item name, plus the item code if you use one
• Batch number, kept as text
• Expiry date, one real date per row
• Closing quantity for that batch, in the base unit
• Rate or value at cost, so you can rank by money at risk instead of by unit count
• Godown or location, if you have more than one
• Manufacturing date, optional, but useful for spotting old stock that came in late

The stock file can't answer the third question, whether it'll sell in time. For that you need sales: a separate export of outward quantity per item over the last few weeks, from the sales register or the item's monthly movement. Without it you only know what's close to expiry, not what's actually stuck.

For example, suppose one batch has 40 units expiring in 30 days. If that item sells 3 a day, it clears in about two weeks and there's nothing to do. If it sells 0.5 a day, you'll move roughly 15 units before expiry and the other 25 are your problem. Same expiry date, very different action, and only the sales column shows you which one you're looking at.

Export mistakes that break the file

These turn up again and again in Tally exports, and each one gives you a wrong answer without any error showing:

• Batch numbers losing their zeros. Excel reads "00412" as the number 412. Format the batch column as text, or import the CSV through Excel's import wizard and set that column to text.
• Dates flipping or turning into text. Tally might write a date one way while your Excel regional settings expect another. Then 03-04 becomes 4 March, or the date sits there as text and won't sort. Sort by expiry once and look at the top and bottom rows. If they look odd, the dates didn't come through properly.
• Group subtotal rows mixed in with items. If you exported with stock groups showing, subtotal lines sit among the real rows and get counted twice. Filter them out, or export with groups collapsed and items expanded.
• The grand total row at the bottom. Delete it before you sum anything.
• Merged header rows. Company name, period and report title sit above the column headers. Strip them so row one is just the headers.
• "Primary batch" or blank-batch lines. Stock entered before batches were switched on shows up without a batch number or expiry. Don't delete it. Mark it as "expiry unknown" and check it physically, because that's usually your oldest stock.
• Negative closing quantities. These point to billing done before purchase entry, not real stock. Fix the entries in Tally, because the export will just copy the error.

Cleaning it into a usable sheet

Once the file is clean, add one formula column: days to expiry, which is the expiry date minus today. Then add a value column (quantity × cost rate) if the export didn't include one. Sort by days to expiry, filter to the next 60 or 90 days, and then sort that shorter list by value.

Now you've got a working expiry list. The highest-value batches near expiry go to the top, where a markdown, a return to the distributor or a move to another counter actually makes a difference. If you'd like a ready layout, the expiry-tracking spreadsheet template already has these columns.

Be honest about what the sheet can and can't do. It's a snapshot. It goes stale the moment the next bill is printed, and it only works if someone exports, cleans and sorts it every week. Plenty of stores do this well for months, then a busy season comes and the export stops happening.

Running the check every day

If the weekly export keeps slipping, the next step is having something read it for you. Plugins that sit inside Tally are one route, and we compare those in Tally expiry tracking plugins. The other route is an agent working on the exported file. What a single CSV can tell you is covered in the inventory health check from one CSV export.

ShelfSense by ShelfLifePro works the second way. Export from Tally to CSV today; a direct connection is on request. Once a day it reads your stock, batch, cost and expiry records and ranks what to mark down, clear or reorder by money at risk, with the arithmetic shown. You approve or skip each call, and it never changes a price on its own. ShelfSense's free audit reads one CSV export, no account needed. The Tally expiry alerts page shows what to export, and you can start free here.