nickmaenhout
Posts: 3
Joined: 3/25/2023
From: Port Washington, NY
Status: offline
|
Hello All.
Introduction
I have been looking for a way to visualize the CellarTracker data in a map view, displaying some of the key information such as bottle location, year, value and drink-by date. The result of this endeavor is an Excel-based dashboard which works by using various formatting macros to create a visual map of your cellar. Ideally, it would be great to integrate this into CellarTracker somehow as a built-in GUI or even standalone app, but for now it lives in Excel.
The Ask
Essentially, I am looking for others to test, debug, and offer up improvements to make this cellar map and dashboard better. Below is a breakdown with screenshots of how the file works. I have not had any other users test it yet, so was hoping a few here would be willing to do so. As far as I know there is no way to attach a file directly here, so please PM me if you are interested.
A few items to note before getting into the details:
I created this on Excel for Mac, so there were limitations with what I could code. For example, forms are not allowed on Excel for Mac, so everything needed to be embedded in the sheets themselves.
This is designed to work for any cellar setup, as long as the proper naming convention is used for the "Location" and "Bin" fields in CellarTracker. More details on this are explained later.
As mentioned above, this has only been tested on a Mac so far, so I am curious how it performs on Excel for Windows.
I am not a programmer or coder by trade. This was just a pet project I worked on using my Excel knowledge gained from a career in the finance industry. I will be dependent on others with more expertise to optimize and debug this over time.
Lastly, my ultimate goal here would be to have something like this be integrated into CellarTracker directly, whether through a standalone app or web-based GUI. Either way, I think there is a real demand for something like this to be available to the average wine collector, outside of Excel.
File Structure
There are 2 core sheets + a sheet per however many cellars you create
- Dashboard Sheet - Main sheet which allows you to refresh data, generate a new cellar and display some basic metrics about your collection
- CellarTracker Data - Data sheet which houses the imported CellarTracker data (obtained through WebQuery)
- Cellar Sheet(s)* - Unlimited amount of sheets that you can create for each 'cellar' that you want to map. For example, you may want to map a cellar in the basement plus a separate wine fridge upstairs.
Sample Cellar Map
Dashboard Sheet
Generate a new cellar (dynamically based on your rack size and count)
Refresh data directly from CellarTracker via WebQuery into Excel
Provide top level metrics on your cellar such as overall counts, types and drink-by milestones
Dashboard Details
CellarTracker Credentials
Type in your login and password. Password will be hidden on sheet but show in formula bar.
Refresh button will automatically bring in all of your wine data to the CellarTracker Data tab.
This only refreshes the data, so you must have existing cellar(s) mapped to display data visually.
Bottle Metrics
Provides some basic count metrics directly from CellarTracker Data tab.
Currently limited to 3 types of wine, but can possibly be expanded later to offer other metrics.
Setting Up New Cellar
1. Enter a chosen Cellar Name. This will become a new sheet name.
2. Select how many sections you have. In my case, my cellar is sectioned by varietal and region, and my wine fridge is just 1 section.
3. Select how many racks (rows) you have. This will apply equally to each section.
4. Select how many columns you have. This will apply equally to each section.
5. Table will dynamically populate below based on the amount of section selected.
6. Click on Generate to run macro that establishes a new sheet for the cellar, based on your selected attributes.
7. Click on "Toggle Guide and Example" for sample of how the naming convention works (details below)
Naming Convention (CellarTracker Fields)
LOCATION = The name of the cellar where your bottle is located (i.e. Cellar 1, Wine Fridge, etc.).
BIN = The Rack (Row #) and Column (Letter) where your bottle is located (i.e. R1-A = Rack 1, Column A or R7-E = Rack 7, Column E)
As mentioned earlier, example of this can be toggled in the Dashboard sheet, which brings up the below:
Cellar Map Format
Once generated, each bottle will be mapped to show the following
- Wine Name + Year
- Drink By date (highlighted if current year or past prime)
- Value
- Varietal (color coded to match reds, whites and rosé)
Example of Multi-Rack Cellar Setup
< Message edited by nickmaenhout -- 1/20/2024 11:36:55 AM >
|
Printable Version
Cellar Map & Dashboard (Excel) - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread