Page Comparison

Avantalytics and Excel play really nicely together. You can quickly and easily export reports from Avantalytics into Excel, from which you can create presentations and pivot tables - anything you want in Excel. One of the handiest things Avantalytics lets you do is create WebQueries that you can embed into an Excel workbook and refresh as often as your POS sales data refreshes.

Note: Excel 365 users may not see the same import interface described below.  If this is the case, please see the related link on the right of this page to re-enable the legacy import option.

Step-by-step guide

This how-to guide assumes you already know how to run reports. If you need help learning about reports first, please visit our documentation page on reporting or, if you have less time, try our how-to article on running reports. Once you have learned how to run reports, follow the steps below to learn how to embed a WebQuery into an Excel workbook.

1. Run your report. In this how-to, we have run the Weekly Out of Stock Analysis by Item (117) report, which is a perfect report to set up a WebQuery. Setting up a WebQuery for this report is a very easy way for you to check every week to check up on what items are out of stock where and how this is trending overall. Once you have run your report, select the "Create WebQuery" option from the upper-right hand side of the report screen:
   
   
   
   
2. This will bring up a screen that prompts you to complete some information. The "Note" field allows you to write any internal notes you might want to make about the report for later reference and is optional. The "Choose Type" field allows you to make a temporary or a permanent link. For the purposes of this tutorial, our aim is to teach you how to build an automatically refreshing Excel workbook for yourself, so choose "Permanent." You would choose "Temporary" if you were, for example, sending a report to a client where you want the client to have access for perhaps a week. Once you have entered your notes, you will hit "Create" on the bottom right. A link will appear below the "Note" section. The first link is designed for sending to someone as a webpage link and therefore will not work for your Excel workbook. If you just want a WebQuery link to email to someone, congratulations, you are done! Copy the link and email it to whoever needs the link. If you want to learn how to get the Excel-ready link and create the workbook, click the "Copy" next to the second link where Excel URL is indicated and proceed to the step 4 or review the step 3 to get to any of your other existing webquery URLs.
   
   
   
   
   
   
3. Once back on the main page, navigate to the "Reporting" tab under "Main Menu" and, when it expands, click on "WebQuery Access:" All WebQueries you have created will be in the WebQuery Access, and you can manage all of your links that you have created from this view. WebQueries are listed by report name alphabetically by default, so we'll locate the "Weekly OOS Analysis By Item" and click "Copy Excel URL." This is also where you can deactivate permanent links if you have decided you no longer want a particular WebQuery.
   
   
   
   
   
   
4. Open a new Excel workbook.  Depending on your version of Excel the screens and process may appear slightly different.  Excel 365 users can also view the link here to see how to achieve this with Microsoft's newer interface.
   
   
5. Navigate to the "Data" tab and select "From Text:"
   
   
   
   
6. A window will pop up, and you will paste your WebQuery link in the data field labeled "File Name," then click "Open," which will open an import wizard:
   
   
   
   
7. The import wizard will have three screens. In the first screen, make sure "Delimited" is selected, and check the box "My data has headers", then click "Next:"
   
   
   
   
8. In the second screen, "Tab" is usually checked automatically. Make sure this box is unchecked, and instead make sure "Comma" is selected:
   
   
   
   
9. In the third screen, make sure "General" is selected, and then hit "Finish:"
   
   
   
   
10. An Import Data window will appear. Click "Properties:"
    
    
11. Make sure the box "Prompt for file name on refresh" is unchecked and click "OK." You will be returned to the above "Import Data" window. Click "OK."
    
    
    
    
12. You now have a WebQuery ready Excel workbook! Any time you need to refresh the data, go to the "Data" tab and click "Refresh All." You can also right click on your mouse on any cell in the workbook and select "Refresh" form the drop down menu that appears:
    
    
    
    
    

Related articles