Spreadsheet Grocery Budget Management

Updated on 2022-12-06

The process of using a spreadsheet to manage my grocery budget began in 2018 when I put together a LibreOffice Calc spreadsheet workbook (file of multiple spreadsheets), titled price–history, to track prices of purchased items. I wanted to keep track of prices so that I could recognize price increases, both obvious or phantom; e.g. quantity reductions for the same price. I mainly use receipts from my shopping trips to populate the spreadsheet, but I sometimes go from memory and I’ve also taken pictures of shelf prices using my smartphone.

Up until about two and a half years ago, I didn’t worry much about staying within my grocery budget. I did have a budget and I tracked what I spent on groceries, but I frequently went over budget. With no income and having gone through all of my retirement money, my grocery budget became much smaller when I began relying on the generosity of my wife.

Initially, I put together a rough LibreOffice Calc spreadsheet, naming it grocery–shopping–list, as an aid in budgeting my monthly grocery purchases. However, too many times I didn’t make the time to use the spreadsheet and it was cumbersome to use for purchases from multiple stores. After going over budget on a monthly basis for most of 2020, I decided things needed to change. So I made time to put together a more sophisticated grocery shopping list spreadsheet workbook that better enables me to stay within my monthly grocery budget.

I currently shop at three different stores for my grocery needs; Walmart, Wegmans, and Whole Foods Market. Therefore, my grocery shopping list spreadsheet workbook contains a separate spreadsheet for each of the aforementioned stores and a spreadsheet to cover planned purchases at other stores, physical and online.

Product pricing is obtained from my price history spreadsheet workbook. When price changes occur, I record the change in the price history spreadsheet workbook. I use the most current product price when planning my monthly and future grocery purchases.

I have a summary spreadsheet that combines all of the data from the separate store spreadsheets using formulas and range names. My monthly grocery budget available balance is recorded on the summary spreadsheet.

I have a sales tax spreadsheet that has the sales tax rates for taxable items in my state. The rates are used via range names in formulas in the seperate store spreadsheets to generate totals for purchases.

After using the more sophisticated spreadsheet workbook for awhile, I decided to add a spreadsheet for future purchases and a spreadsheet for items that I purchase on a monthly recurring basis. I copy and paste the data from the monthly purchase spreadsheet into the corresponding store spreadsheets.

I highlighted spreadsheet cells containing formulas in yellow as a reminder not to enter anything into the cells. Entering data into a cell containing a formula would wipe out the formula.

As I spend money, I adjust the available balance on the summary spreadsheet, obtaining the balance from my grocery money set aside account in GnuCash.

Changes can easily and quickly be made since I’m using spreadsheet technology. If I decide I want to add one or more items, I can quickly and easily add the item(s) to the applicable store spreadsheet. If the summary spreadsheet shows that I may overrun my monthly grocery budget, I can move items to the future purchases spreadsheet.

When I’m ready to go shopping at one of the stores at which I regularly shop, I go old school and handwrite a shopping list, writing down the items to purchase from the applicable store spreadsheet. When I get back from shopping, I remove those items that I have purchased from the applicable store spreadsheet.

Since creating and using the more sophisticated spreadsheet workbook, I have not exceeded my monthly grocery budget. In fact, there have been some months that have ended with a surplus.

Hopefully before the year is over, I’d like to learn SQLite to see if the database format can be used as a replacement for my purchase price history spreadsheet. If SQLite can be used to create a price history database, then I should be able to use the PortoDB app on my smartphone and always have pricing information with me whenever I go shopping.

You can download the purchase price spreadsheet workbook and the grocery shopping list spreadsheet workbook here, as a 7-Zip (7z) compressed file. The compressed file contains spreadsheet workbooks in both OpenDocument spreadsheet format (.ods) and Microsoft Excel format (.xlsx). You should also download and decompress the GPG signature for the compressed file to verify that it has not been tampered with. You will also need the Shared Bits public key and verification software.

I suggest making copies of the spreadsheet workbook files, renaming the copies for your use. Making copies will allow you to refer back to the originals in case you accidently erase something or change a formula that ends up not working correctly. You’ll also need to change the some of the tab names and some of the range names in the grocery shopping list spreadsheet workbook to reflect the stores at which you shop.

2022-12-05 Update

---

I grew tired of usually having to wait until the end of the month to begin planning my grocery shopping for the next month. I often felt a bit stressed about having to begin planning my grocery shopping at the beginning of the month because I do a number of other computer-related activities at the beginning of the month and I also begin doing some of my grocery shopping the first week of the month, often on the first or second day.

Therefore, in July 2022 I came up with the idea of adding a column on the summary spreadsheet for the next month grocery budget and making separate spreadsheets for each store that I shop at for the next month. My idea turned out to work much better than the single month grocery budget management workbook that I have been previously using.

Throughout the current month, I’ll begin planning my grocery shopping needs for the following month. At the end of the month, I’ll copy the contents of the next month spreadsheets to the current month spreadsheets. The copying process could be automated by creating a macro, but I haven’t made the time to work on that yet.

I also began shopping for groceries at an additional store, so the spreadsheet workbook has been also been updated to accomodate planning for shopping at 4 stores.

The grocery shopping list spreadsheet workbook downloads have been updated to include my new idea. I also began shopping for groceries at an additional store, so the spreadsheet workbooks have also been updated to accomodate planning for shopping at 4 stores. If you don’t want to use the next month budgeting feature, you can still just use the current month budget spreadsheets in the workbook.

I envisioned eventually using a SQLite database to track prices. However, SQLite is designed more for use by application developers who need a relational database backend for their application. I just wanted something to use as an end user. I eventually decided to give LibreOffice Base another look.

In October 2022, I imported my LibreOffice Calc price spreadsheet data into a LibreOffice Base price database that I created. The file download now also includes the price database (*.odb) that I created. The price spreadsheet workbooks are still available for those who aren’t comfortable working with database technology or who do not use LibreOffice, but I will probably no longer update them.

Database tables require a unique key for each record. I decided to use the purchase date and an incremental number by purchase date, as the unique key (ID field); e.g. 20221001-01, 20221001-02, 20221115-01, 20221115-02, etc.

If you’re interested in learning LibreOffice Base, checkout my post on learning LibreOffice for resources.

Feel free to leave your question or comment in the Comment box at the bottom of the page.

2021-006