41

It happened again today. We were at three different grocery stores, and still managed to not buy some of the stuff that we need. I'm getting tired of coming home just to realize I need to go back out again, so I need to automate the tracking of what we consume as a family as much as possible.

I'm looking for software that will run on Windows 7 or higher, which does the following things:

  • Tracks what I buy, based on me telling it what I've bought
  • Knows how much of each thing that we typically use, based on how often I tell it that I've purchased something
  • Gives me a list of stuff that I'm probably out of when it's time to go to the grocery store, and gives me a way to look at its understanding of what we probably have in stock at any point in time.

Basically, a system that learns our consumption over time based on what we buy, and how often we buy it.

Nice to have:

  • Easy to use mobile app (iOS/Android) which lets me log when I buy something and what I've bought. The easier the better, if it can understand UPC / QR codes, that would be great.
  • Easy generation of a shopping list that I can either print, email, or send to my mobile device
  • Price tracking (e.g. you use approximately $1.23 worth of garbage bags per week)

I wouldn't be opposed to something that tied into some kind of web service, but I'm really hoping for a (mostly) stand-alone desktop / phone app. By this, I mean I'd like to interact with the software exclusively on my desktop or mobile device, not through a web interface bolted on to whatever shares the information between my devices.

Does something like this exist? Can you help me not buy a tub of butter three weeks in a row resulting in a surplus that took us months to consume?

  • I know basic grocery list apps but nothing with such "learning" capabilities (learns what stuff you buy regularly)... That's a nice idea for a side project. – user111 Sep 14 '14 at 1:28
  • @AndréDaniel Yes, it seems to me that would work well, since most of my everyday consumables seem to be bought on some kind of schedule. As long as it had some tools to account for vacations, pure luxury items that you might not always buy (and some kind of holiday mode) I think it could work rather well and reliably. – Tim Post Sep 14 '14 at 4:47
  • 10
    Tim is just hoping for the Manishearth effect to happen again ;) – Seth Sep 14 '14 at 15:59
  • What would the QR codes be for? Do you just mean standard UPCs to search for price? Also: how can it be not a web service but accessible on your phone unless you email it to yourself? – Anonymous Penguin Sep 15 '14 at 22:08
  • By 'scan code' I meant UPC, which really changes that sentence a bit. I just want to be able to clock in what I purchase in whatever way is most convenient. – Tim Post Oct 9 '14 at 7:16
8

After laughing seriously, I got to realize your question is quite reasonable! I don't really know if there is an app like this, however you can set up Excel to do this for you or even use a BI feature, such as Tableau Public which is absolutely free. You can then set up various categories but you'll need to invest a while to enter your shopping info for a month.

Tableau allows forecasting, so you will basically have the option to figure out (based on let's say your groceries for 3 months) how much of each product you are going to need. I don't really know however how to solve the problem with having a software suggest what you may need to buy.

But your question is indeed an awesome business idea...

4

You're going about it the wrong way. Instead of automating a reminder to buy some, just have them shipped to your house on a regular basis.

The first thing that comes to mind is Alice.com, but that site is undergoing a reboot.

The next alternative would be Amazon Subscribe and Save http://amzn.to/1o01r0X

Going beyond garbage bags though, that's were it would have been nice if RFID UPC tags would have taken off. Then you could ask your cabinets when you put something in it and at least make a guess on if they are needed (or programmatically somehow).

I'm making an assumption here that most people are unwilling to scan everything they buy into some kind of tracking app.

You could have readers on garbage cans too so when you throw the box away, you know you're out.

2

ONLY Part of the answer:

You still need to get the raw data into the database, and transfer the calculated restock date to a calendar.

SELECT "Description", DATEDIFF( 'dd', MIN( "Posting Date" ), MAX( "Posting Date" ) ) / COUNT( * ) AS "Days between purchase", SUM( "Amount" ) / COUNT( * ) AS "Average Price", DATEDIFF( 'dd', MIN( "Posting Date" ), MAX( "Posting Date" ) ) /count(*) as "Average Days Item Lasts", DATEDIFF('dd',MAX( "Posting Date" ),NOW()) as "Last Purchase # Days ago" FROM "credit" GROUP BY "Description"

I got this working in LibreOffice. Unfortunately, Libreoffice doesn't support date_add so I couldn't give you a re-order date.

Here is the better version for mysql, tested

SELECT Description, DATEDIFF( MAX( `Posting Date` ), MIN( `Posting Date` ) ) / COUNT( * ) AS `Order every # Days`, SUM( `Amount` ) / COUNT( * ) AS `Average Price`, MAX( `Posting Date` ) as `Last Purchased` ,DATE_ADD( MAX( `Posting Date` ), INTERVAL DATEDIFF( MAX( `Posting Date` ), MIN( `Posting Date` ) ) / COUNT( * ) DAY ) as `You will run out on` FROM `credit` GROUP BY `Description`;

Sample results (mysql):

Description Order every # Days  Average Price   Last Purchased  You will run out on
Item 24     67.2857 29.8571428571429    06/08/16 12:00 AM   08/14/16 12:00 AM
Item 4      113     90                  04/06/16 12:00 AM   07/28/16 12:00 AM
Item 57     37.0909 152.545454545455    06/21/16 12:00 AM   07/28/16 12:00 AM
Item 9      55.5556 54.8888888888889    05/27/16 12:00 AM   07/22/16 12:00 AM
Item 28     18.9286 127.035714285714    07/03/16 12:00 AM   07/22/16 12:00 AM
Item 42     16.5882 11.7058823529412    07/05/16 12:00 AM   07/22/16 12:00 AM
Item 35     10.5385 21.0192307692308    07/06/16 12:00 AM   07/17/16 12:00 AM
Item 12     12.6098 65.2439024390244    07/03/16 12:00 AM   07/16/16 12:00 AM
Item 25     22.2917 6.66666666666667    06/22/16 12:00 AM   07/14/16 12:00 AM
Item 38      9.4038 29.3846153846154    07/03/16 12:00 AM   07/12/16 12:00 AM
Item 3      17.5667 35.0333333333333    06/22/16 12:00 AM   07/10/16 12:00 AM

Added order by so ordered better, and cut the list for 7 days for a weeks worth of shopping.

SELECT `Category`, DATEDIFF( MAX( `Posting Date` ), MIN( `Posting Date` ) ) / COUNT( * ) AS `Order every # Days`, SUM( `Amount` ) / COUNT( * ) AS `Average Price`, MAX( `Posting Date` ) as `Last Purchased` ,DATE_ADD( MAX( `Posting Date` ), INTERVAL DATEDIFF( MAX( `Posting Date` ), MIN( `Posting Date` ) ) / COUNT( * ) DAY ) as `You will run out on`
FROM `credit`
GROUP BY `Category`
HAVING `You will run out on` < (NOW() + INTERVAL 7 DAY)
ORDER by `You will run out on` DESC;

If you create a database in microsoft access or libre office base, the above select statement should do most of the heavy lifting. I did this in a mysql database for testing, but besides minor tweaks it should work.

  1. Note timestamp is both the name of the column, and datatype
  2. timestamp is DATE/TIME in access
  3. You need an item column as string to store what your buying.
  4. Column named price for the price.
  5. The formula will have to be changed if you buy different quantities. Say 64oz of something 1 time and 128oz another.
  6. Quantity as in I bought 5 boxes, not 48oz or 10lbs.

How to create table:mysql

MariaDB [test]> describe credit;
+----------------------------+--------------+------+-----+---------+----------------+
| Field                      | Type         | Null | Key | Default | Extra          |
+----------------------------+--------------+------+-----+---------+----------------+
| ID                         | int(11)      | NO   | PRI | NULL    | auto_increment |
| Originating Account Number | varchar(100) | YES  |     | NULL    |                |
| Posting Date               | datetime     | YES  |     | NULL    |                |
| Trans Date                 | datetime     | YES  |     | NULL    |                |
| Type                       | varchar(100) | YES  |     | NULL    |                |
| Category                   | varchar(100) | YES  |     | NULL    |                |
| Merchant Name              | varchar(100) | YES  |     | NULL    |                |
| Merchant City              | varchar(100) | YES  |     | NULL    |                |
| Merchant State             | varchar(100) | YES  |     | NULL    |                |
| Description                | varchar(100) | YES  |     | NULL    |                |
| Transaction Type           | varchar(100) | YES  |     | NULL    |                |
| Amount                     | double       | YES  |     | NULL    |                |
| Reference Number           | varchar(100) | YES  |     | NULL    |                |
+----------------------------+--------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.