Extract data with web automation, integrate with VBA/Excel, make results available to company through web or email alerts
In broad outline we would like to automate the following process:
1. Every five minutes extract the sales data for three retail locations through the Vend POS web application (http://www.vendhq.com/)
2. Paste that data extracted in 1 into an Excel spreadsheet that measures it against average performance for that hour/day (this is different for each store, each day and each hour)
3. Post a summary page of 2 onto a private website that the company can access (happy to use Sharepoint/Office 365 if that makes things easier). Icloud would be most convenient - but not essential.
4. Using VBA, send email alerts where certain conditions are satisfied (e.g. sales at a given point of the day are 20% higher than the average or number of units sold per hour is 20% higher than average)
5. We should also get alerts when there is a problem with the program or Vend.
Our experience is that the Vend API is not user friendly and cannot give us the data we require, so we would prefer to manage this through web automation if possible.
NB - you will need to use safari or chrome, as these are the only browsers that Vend supports.
We would like to run this from our PC or iMac (always on, always online) at our headquarters.
We do not really want to get into proper databases - we would like to keep things simple. However, we may be open to using Access if you can set it up in a user friendly way.