Difference from prior author revision.
major diff minor diff hide diff
CellarTracker makes it easy for users to download a copy of their cellar to an 
Excel file. 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).
  
<TableOfContents>
  
= 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.<br>//Please note that as of 10/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
  1. Open this spreadsheet and go to the first tab: **LOGIN**
  1. Enter your CellarTracker handle and password in the provided spaces. 
//(You will only need to do this once.)//
  1. 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.<br>
  https://www.cellartracker.com/images/ExternalRefresh.gif<br><br>
  1. 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.)//<br>
  https://www.cellartracker.com/images/RefreshAll.gif
  
= Troubleshooting =
**My sheet doesn't seem to be updating, what can I do?**
  * Are your handle and password correct on the **LOGIN** 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?<br>
**A:** There are two ways to get at this.<br>
  1. Either switch from the **WINE LIST** tab to the **INDIVIDUAL BOTTLES** tab.
  1. 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 On the Data Ribbon, you click on Connections, pick a
connection, the Connections button. Then you select the first Connection and
choose the Properties button. Then you switch to the Definition tab and click on
Properties. Then there is a the Parameters button at button. Finally you select
the bottom Location parameter and change the value from 0 to 1. Click OK, OK and
Close to get out of the screen.<br> three level dialog tunnel. Finally
refresh the Wine List tab, and now you will see the Location and Bin
columns.<br>
  https://www.cellartracker.com/images/QueryParam.gif<br>
  Click on the **Location** parameter and set a value of **1** as shown 
below.<br>
  https://www.cellartracker.com/images/Location.gif<br>
  
**Q:** Can I enter information in this Excel spreadsheet and have it update the 
main CellarTracker site?<br>
**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 
BulkImport.
  
**Q:** Can I use spreadsheets other than Microsoft Excel?<br>
**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:<br>
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.<br><br>
**Wine Summary (without Location):**<br>
https://www.cellartracker.com/xlquery.asp?table=List&User=<your_login>&Password=<your_password><br><br>
**Wine Summary (with Location):**<br>
https://www.cellartracker.com/xlquery.asp?table=List&Location=1&User=<your_login>&Password=<your_password><br><br>
**Individual bottles:**<br>
https://www.cellartracker.com/xlquery.asp?table=Inventory&User=<your_login>&Password=<your_password><br><br>
**Your Tasting Notes:**<br>
https://www.cellartracker.com/xlquery.asp?table=Notes&User=<your_login>&Password=<your_password><br><br>
**Private Notes:**<br>
https://www.cellartracker.com/xlquery.asp?Table=PrivateNotes&User=<your_login>&Password=<your_password><br><br>
**Purchases:**<br>
https://www.cellartracker.com/xlquery.asp?table=Purchase&User=<your_login>&Password=<your_password><br><br>
**Pending Purchases: (Futures)**<br>
https://www.cellartracker.com/xlquery.asp?table=Pending&User=<your_login>&Password=<your_password><br><br>
**Consumed:**<br>
https://www.cellartracker.com/xlquery.asp?table=Consumed&User=<your_login>&Password=<your_password><br><br>
**Drinkability Report:**<br>
https://www.cellartracker.com/xlquery.asp?table=Availability&User=<your_login>&Password=<your_password><br><br>
**Wishlist:**<br>
https://www.cellartracker.com/xlquery.asp?Table=Tag&User=<your_login>&Password=<your_password><br><br>
**Professional Reviews (user entered):**<br>
https://www.cellartracker.com/xlquery.asp?Table=ProReview&User=<your_login>&Password=<your_password><br><br>
  
= 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 
[http://www.acsu.buffalo.edu/~gordonj/XL/DownloadPage.htm 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.<br>
Supported Browsers: Internet Explorer 5.2 or later, Firefox 1.0 or later, 
Camino 0.8 or later<br>
**NOTE:** Safari is NOT SUPPORTED due to text handling issues with 
tabs<br>
<br>
To create an offline copy of your cellar Excel v.X for Mac OS X<br>
  1. Launch a supported web browser (Safari will NOT work)
  1. Enter the desired URL for your query (see Creating and Editing 
CellarTracker Web Queries below) with your login and password
  1. Once the web page loads, do a Select All (command A) and Copy (command C) 
all the contents of the query.
  1. Launch Excel and open an existing or create a new worksheet
  1. In a new worksheet, do a Paste (command V) to insert the previously copied 
web page
  1. Excel will paste the tab-delimited contents correctly into columns as 
desired. Save the worksheet.
  
**Update 2010.01.04 (someone please confirm)**
  
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.
  
-------
CategoryFAQ
Excel Download
