Export to Excel

The easiest way to export to Excel is to click MY CELLAR and then look for the EXPORT button at the upper right. The site will let you choose which records and which columns to export and whether to download as text or CSV.

Alternatively, there is also a legacy Excel tool you can download. This acts like a standalone application in that you feed it your CellarTracker handle and password, and then it can be refreshed to grab a copy of the latest data from your CellarTracker account. This is useful as a personal backup, for offline access (on a plane or to sync to a handheld device), and for deeper analysis using the rich tools in Microsoft Excel. Or for those without Excel, you can also download to XML, HTML, tab-delimited text, or comma-separated text (CSV).

Steps

  1. First download a copy of the special Excel spreadsheet found at https://www.cellartracker.com/webquery.xls and save this to your local machine.
    Please note that as of October 27, 2011 there is a brand new version of this file designed to use all HTTPS links for better password security, so please download the latest. However there are some reports of people having difficulty with this in Mac Excel 2008, so I have also posted an Insecure version that sends your password in plaintext. Please use it with caution: http://www.cellartracker.com/Webquery_Insecure.xls
  2. Open this spreadsheet and go to the first tab: Login
  3. Enter your CellarTracker handle and password in the provided spaces. (You will only need to do this once.)
  4. You may have seen a prompt (shown below) when loading the file about enabling external data refresh. Depending upon how you answered the sheet may (not) already be downloading as soon as you update the handle and password.

  5. If not, please bring up the EXTERNAL DATA toolbar and click the REFRESH ALL button shown below. (If the toolbar is not showing then RIGHT-CLICK on any other toolbar and choose External Data from the list.)

Troubleshooting

My sheet doesn't seem to be updating, what can I do?

  • Are your handle and password correct on the handle tab? (Note that your handle is not usually your email address.)
  • Have you pressed the REFRESH ALL button shown above?
  • Double-check your firewall and anti-virus software to ensure that it allows Microsoft Excel to access the Internet.

Other Frequently Asked Questions

Q: How can I see Location and Bin information in the Excel download?
A: There are two ways to get at this.

  1. Either switch from the WINE LIST tab to the INDIVIDUAL BOTTLES tab.
  2. Set a non-default option to cause the WINE LIST tab to include Location and Bin columns. Switch to this tab and click into the first or second row of data. In older versions of Office you click the QUERY PARAMETERS button on this toolbar. Sadly in more recent versions of Office this has been buried terribly. You have to go to Data Ribbon, click on Connections, pick a connection, and click on Properties. Then there is a Parameters button at the bottom of the screen.


    Click on the Location parameter and set a value of 1 as shown below.

Q: Can I enter information in this Excel spreadsheet and have it update the main CellarTracker site?
A: Sorry, the Excel spreadsheet described here is a one-way download and not a two-way sync tool. That said, if you are wondering how to import an existing Excel spreadsheet into your CellarTracker account, please read about Bulk Import.

Q: Can I use spreadsheets other than Microsoft Excel?
A: We are not aware of other spreadsheet which support the Web Query feature that allows Microsoft Excel to automatically pull data from the web. That said, CellarTracker simply exposes tabular views onto different aspects of your cellar data, so you could easily copy and paste this from your web browser into OpenOffice or other spreadsheet software. Or using the information below, you can export XML, tab-delimited text or CSV text.

The URLs available for web queries are:
By default the export format is HTML. You can append &Format=xml or &Format=tab or &Format=csv in order to change the output format. Note that if you paste these URLs into a browser that is logged in to your CellarTracker account, you can omit the User and Password variables as these are fetched via Cookies.

Wine Summary (without Location):
https://www.cellartracker.com/xlquery.asp?table=List&User=your_handle&Password=your_password

Wine Summary (with Location):
https://www.cellartracker.com/xlquery.asp?table=List&Location=1&User=your_handle&Password=your_password

Individual bottles:
https://www.cellartracker.com/xlquery.asp?table=Inventory&User=your_handle&Password=your_password

Your Tasting Notes:
https://www.cellartracker.com/xlquery.asp?table=Notes&User=your_handle&Password=your_password

Private Notes:
https://www.cellartracker.com/xlquery.asp?Table=PrivateNotes&User=your_handle&Password=your_password

Purchases:
https://www.cellartracker.com/xlquery.asp?table=Purchase&User=your_handle&Password=your_password

Pending Purchases: (Futures)
https://www.cellartracker.com/xlquery.asp?table=Pending&User=your_handle&Password=your_password

Consumed:
https://www.cellartracker.com/xlquery.asp?table=Consumed&User=your_handle&Password=your_password

Drinkability Report:
https://www.cellartracker.com/xlquery.asp?table=Availability&User=your_handle&Password=your_password

Wishlist:
https://www.cellartracker.com/xlquery.asp?Table=Tag&User=your_handle&Password=your_password

Professional Reviews (user entered):
https://www.cellartracker.com/xlquery.asp?Table=ProReview&User=your_handle&Password=your_password

Macintosh Specific

Unlike Excel for Windows, Excel for Mac OS X does not allow you to edit an existing web query embedded in a spreadsheet. If you wish to create or edit your own CellarTracker web queries, you can download the free Web Table Report tool from the University of Buffalo that offers an easy way to create and edit Excel web queries with Excel for Mac OS X.

There have been reports of issues downloading data to Excel v.x (while Excel 2004 works well). If you run into this, you will need to manually copy and paste web queries from a supported web browser into an Excel v.X worksheet.
Supported Browsers: Internet Explorer 5.2 or later, Firefox 1.0 or later, Camino 0.8 or later. NOTE: Safari is NOT SUPPORTED due to text handling issues with tabs

To create an offline copy of your cellar Excel v.X for Mac OS X

  1. Launch a supported web browser (Safari will NOT work)
  2. Enter the desired URL for your query (see Creating and Editing CellarTracker Web Queries below) with your handle and password
  3. Once the web page loads, do a Select All (command A) and Copy (command C) all the contents of the query.
  4. Launch Excel and open an existing or create a new worksheet
  5. In a new worksheet, do a Paste (command V) to insert the previously copied web page
  6. Excel will paste the tab-delimited contents correctly into columns as desired. Save the worksheet.

Update 2010.01.04: Based on my tests, the web query Excel file (https://www.cellartracker.com/webquery.xls) works well with Microsoft Excel 2008 for Mac, the latest version of Excel for Mac. For me, it works as advertised with the following change: the "Refresh All" option doesn't occur, so instead, place your cursor in the first cell of each sheet (for example, cell A2 of the Tasting Notes sheet) and select Refresh Data from the Data menu. This will populate the spreadsheet with your online data. Repeat this step for each sheet.

Update 2010.04.29: It works very well on Microsoft Excel 2008 for Mac. Actually, unlike the above user, "Refresh All" works just fine. Try it.

Update 2010.05.31: I'm the person who posted the "2010.01.04" update, and I can confirm that the "Refresh All" method works, as reported above in the "Update 2010.04.29" post. Excel 2008 does not automatically load the required "External Data" toolbar, which houses the "Refresh All" button. The Step 5 in the directions at the top of the page address this issue for Windows versions of Excel, and the directions hold true for Excel 2008 for Mac as well. I missed this the first time. To reiterate: open the toolbar by going to View>Toolbars>External Data in the menu bar at the top of your desktop. Clicking the Refresh All button will populate all Excel tabs with the latest data on CellarTracker.

×
×