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).
<a href="#h2">Troubleshooting</a>
</li>
<a href="#h3">Other Frequently Asked Questions</a>
</li>
<a href="#h4">Macintosh Specific</a>
</li>
</ul>
First download a copy of the special Excel spreadsheet found at
<a href="https://www.cellartracker.com/webquery.xls" class="external" target="_blank">https://www.cellartracker.com/webquery.xls</a>
</b>
and save this to your local machine.
<i>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:</i>
<a href="http://www.cellartracker.com/Webquery_Insecure.xls" class="external" target="_blank">http://www.cellartracker.com/Webquery_Insecure.xls</a>
</li>
Open this spreadsheet and go to the first tab:
</li>
Enter your CellarTracker handle and password in the provided spaces.
<i>(You will only need to do this once.)</i>
</li>
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.
</li>
<img src="https://www.cellartracker.com/images/ExternalRefresh.gif" alt="" />
If not, please bring up the
toolbar and click the
button shown below.
<i>(If the toolbar is not showing then RIGHT-CLICK on any other toolbar and choose External Data from the list.)</i>
</li>
<img src="https://www.cellartracker.com/images/RefreshAll.gif" alt="" />
</ol>
<b>My sheet doesn't seem to be updating, what can I do?</b>
Are your handle and password correct on the
tab?
<i>(Note that your handle is not usually your email address.)</i>
</li>
Have you pressed the
button shown above?
</li>
<li>Double-check your firewall and anti-virus software to ensure that it allows Microsoft Excel to access the Internet.</li>
</ul>
<h1>Other Frequently Asked Questions</h1>
How can I see Location and Bin information in the Excel download?
There are two ways to get at this.
Either switch from the
tab to the
<b>INDIVIDUAL BOTTLES</b>
tab.
</li>
Set a non-default option to cause the
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
button on this toolbar. Sadly in more recent versions of Office this has been buried terribly. On the Data Ribbon, you click on the Connections button. Then you select the first Connection and choose the Properties button. Then you switch to the Definition tab and click the Parameters button. Finally you select the Location parameter and change the value from 0 to 1. Click OK, OK and Close to get out of the three level dialog tunnel. Finally refresh the Wine List tab, and now you will see the Location and Bin columns.
</li>
<img src="https://www.cellartracker.com/images/QueryParam.gif" alt="" />
Click on the
parameter and set a value of
as shown below.
<img src="https://www.cellartracker.com/images/Location.gif" alt="" />
</ol>
Can I enter information in this Excel spreadsheet and have it update the main CellarTracker site?
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
<ow:link name="BulkImport" href="ow.asp?BulkImport" date="2011-11-22T15:07:40-08:00">Bulk Import</ow:link>
.
Can I use spreadsheets other than Microsoft Excel?
We are not aware of other spreadsheet which support the
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
or
or
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.
<b>Wine Summary (without Location):</b>
<a href="https://www.cellartracker.com/xlquery.asp?table=List&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=List&User=<your_login>&Password=<your_password></a>
<b>Wine Summary (with Location):</b>
<a href="https://www.cellartracker.com/xlquery.asp?table=List&Location=1&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=List&Location=1&User=<your_login>&Password=<your_password></a>
<b>Individual bottles:</b>
<a href="https://www.cellartracker.com/xlquery.asp?table=Inventory&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=Inventory&User=<your_login>&Password=<your_password></a>
<b>Your Tasting Notes:</b>
<a href="https://www.cellartracker.com/xlquery.asp?table=Notes&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=Notes&User=<your_login>&Password=<your_password></a>
<a href="https://www.cellartracker.com/xlquery.asp?Table=PrivateNotes&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?Table=PrivateNotes&User=<your_login>&Password=<your_password></a>
<a href="https://www.cellartracker.com/xlquery.asp?table=Purchase&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=Purchase&User=<your_login>&Password=<your_password></a>
<b>Pending Purchases: (Futures)</b>
<a href="https://www.cellartracker.com/xlquery.asp?table=Pending&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=Pending&User=<your_login>&Password=<your_password></a>
<a href="https://www.cellartracker.com/xlquery.asp?table=Consumed&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=Consumed&User=<your_login>&Password=<your_password></a>
<b>Drinkability Report:</b>
<a href="https://www.cellartracker.com/xlquery.asp?table=Availability&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?table=Availability&User=<your_login>&Password=<your_password></a>
<a href="https://www.cellartracker.com/xlquery.asp?Table=Tag&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?Table=Tag&User=<your_login>&Password=<your_password></a>
<b>Professional Reviews (user entered):</b>
<a href="https://www.cellartracker.com/xlquery.asp?Table=ProReview&User=<your_login>&Password=<your_password>" class="external" target="_blank">https://www.cellartracker.com/xlquery.asp?Table=ProReview&User=<your_login>&Password=<your_password></a>
<h1>Macintosh Specific</h1>
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
<a href="http://www.acsu.buffalo.edu/~gordonj/XL/DownloadPage.htm" class="external" target="_blank">free Web Table Report tool from the University of Buffalo</a>
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
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
<li>Launch a supported web browser (Safari will NOT work)</li>
<li>Enter the desired URL for your query (see Creating and Editing CellarTracker Web Queries below) with your login and password</li>
<li>Once the web page loads, do a Select All (command A) and Copy (command C) all the contents of the query.</li>
<li>Launch Excel and open an existing or create a new worksheet</li>
<li>In a new worksheet, do a Paste (command V) to insert the previously copied web page</li>
<li>Excel will paste the tab-delimited contents correctly into columns as desired. Save the worksheet.</li>
</ol>
<b>Update 2010.01.04 (someone please confirm)</b>
Based on my tests, the web query Excel file (
<a href="https://www.cellartracker.com/webquery.xls" class="external" target="_blank">https://www.cellartracker.com/webquery.xls</a>
</b>
) 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
of the
sheet) and select
from the
menu. This will populate the spreadsheet with your online data. Repeat this step for each sheet.
It works very well on Microsoft Excel 2008 for Mac. Actually, unlike the above user, "Refresh All" works just fine. Try it.
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
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
>
>
in the menu bar at the top of your desktop. Clicking the
button will populate all Excel tabs with the latest data on CellarTracker.
<ow:link name="CategoryFAQ" href="ow.asp?CategoryFAQ" date="2008-02-14T16:56:59-08:00">Category FAQ</ow:link>
</ow:body>