I would like to be able to complete the following project by the 8th July 2018.
I will download a report daily as an xml file that will be saved with the same name. There will be two reports in total (both in the same format with the same headers), one for each location. All the below will need to be completed so that we can analyse the data for each location and the combined data for both locations.
The structure of the data will be:
• Parent – Category Type
• Child 1 – Category
• Child 2 – Product
• Child 3 – Modifiers
*All the above will then have a variety of data associated with it including Volume, Tax, Time Stamp etc.
A macro will be needed to perform the following:
1. Open a daily XML file as mentioned above (this will be in a standard format with varying data).
2. Correlate a small amount of the data against a reference sheet (in a separate .xls file) to add in an extra column to the raw data.
- All sale items have a “Category” attached to them in the xml file, we will need to attach a “Category Type” to all the categories also allowing for any new categories/Category Types being added into the reference sheet (If there is no corresponding “Category Type” in the reference sheet it should default to a “Category Type of “Uncategorised”.
3. Sort, filter and extract relevant data (indicated in the attached file “XML Map [login to view URL]”)
- This will includes splitting the Product to show the modifiers (these are separated by a “+” and I have given an example in the attached file “Product Modifier [login to view URL]”
4. Produce a Daily report, with calculations, to measure selected KPIs
5. Combine the daily reports into a weekly summary
6. Create a spreadsheet that will track the weekly trends and predict the following weeks sales
7. Automate the import, sort and exporting of both daily report, weekly summary report and running trend and predictions report via a Task Scheduler VBS Script
8. Be able to pull in historic reports for back dated data
All steps to be tested and should account for error handling in the raw data.
There should be a file path line in the final spreadsheet which will be used to export the reports to.
I am happy to answer any questions and will be available for roughly 1-2 hours per day to work with you on this project.