Also interested in the code for this please. Do you need a CellarTracker Premium subscription to get this data?
I have also successfully used this to integrate my wine cellar data. Looks like it doesn’t import as much information as andreasy had about bottles consumed, etc. but still looks very cool.
Now I need to buy more wine…
Just got this integrated and seems to work fine. Data imported fine and made a lot of sensors for each of the “groups” to summarize. I turned it off for now while I modify the python code to customize the data a bit, but the first cut was encouraging.
I’m trying to find a low-overhead way to use the imported data structure to graphically show me my cellar (at least the bottles that have a distinct slot). Maybe even a tool tip that shows me which wine is in what slot when I hover over it. Right now the script is set up to make every data summary a separate sensor, which might get clunky.
That’s about 3 grades beyond my programming ability now, but I’m trying some baby steps.
Big thanks to @mathroule and @andreasy for their work on this and ahoernecke on GitHub.
Looks good! Could you share the code for Consumed and Bought last 6 months card?
would second that…
Any progress on this?
At least i found out that you can add further groups by adding them to the “groups” in the init.py file…
groups = [‘Varietal’, ‘Country’, ‘Vintage’, ‘Producer’, ‘Type’, ‘EndConsume’, ‘CT’, ‘Size’]
No, haven’t worked on it for a while. Work got in the way, and that’s what pays for the wine so . . . 
That said, I’ve been learning pandas and some of the other libraries it uses to make my own data structures and such. I did play around with the groups thing like you did and had success getting only the fields I wanted.
You do have to be careful, as it made a “sensor” for every subregion and producer, I think. Nothing I can’t clean up later but certainly not something I wanted clogging up my HA entities list.
Yeah, found that too… also trying to get hold of pandas.
I want to try and get a list of wines sorted by end drink date and then cut it to just show the top 20 or so. Would make choosing a wine for the evening so much easier
But so far only groups and they dont really help for that.
One thing I found that helps is dumping the excel spreadsheet from Cellartracker (csv, then importing it). Then you can play around with different manipulations there to figure out best ways to sort the data to get what you want. The commands and stuff are different but it gets your head around “Do math on drinkability dates”, sort by X, then sort by Y so you can port that to Python a little easier.
Again, though, I’ve only tested some very basic stuff in HA so far. My goal is to use cellar bins to show bottle locations (using some of the “room map” features of HA, but that’s a long ways off.
Hi all,
I’ve been tinkering with Cellar Tracker and found you can export XML or CSV via a simple https request:
Still trying to figure out how to import the XML into HA and convert into sensors. I’d like a simple list of my wines with the vintage, name, locale and when to drink (begin and end fields in the XML)
Will try to solve it myself but if anyone wants to help…
I can’t help much with programming, but I can help with testing and hunting down bugs.
1 Like
I’ve been able to successfully import the XML from cellartracker, but I’m yet to find a proper way to extract the data into a sensor:
rest:
- resource: https://www.cellartracker.com/xlquery.asp?User=User&Password=Pwd&Format=xml
sensor:
- name: Wine_name
value_template: '{{value | regex_findall_index("<Wine> (_([A-Za-z]+_[0-9])) </Wine>")}}'
so far everything comes back with “unknown”, don’t know how to progress, if anyone has an idea?
I’ve also tried using scrape but no meaningful results
Likely the request would be too large for it to be the state value. You would put into attributes.
I will look at the XML. As an owner of California Winery I have an interest in this.
I do not have a cellartracker account, if you download and post the XML here I can just set it up locally to extract the information. Another possible example is to use command_line with JQ to get CSV and make JSON and parse to attribute(s) on a sensor. I use JQ to isolate and name the data I wish to target because the sometimes the information has alot of things I do not want or need in my sensor.
Here is one I do for the Russian River flood data:
##
## water.weather.gov CSV
##
- sensor:
scan_interval: 3600
name: Russian River Flood Info
command: "curl -s 'https://www.cnrfc.noaa.gov/graphicalRVF_csv.php?id=GUEC1' | jq --raw-input '{flooddata: [inputs | split(\",\") | {issued: .[0], valid: .[1] | strptime(\"%m/%d/%Y %I %p\") | strftime(\"%m/%d/%Y %I:%M %p\"), validts: .[1] | strptime(\"%m/%d/%Y %I %p\") | strftime(\"%Y-%d-%m %H:%M:%S\"), level: .[2] | tonumber, trend: .[3], status: .[4], mode: .[5] | gsub(\"[\\r]\"; \"\") }]| .[2:999] }'"
value_template: >
{% set levels = namespace(lvl=[]) %}
{% for height in value_json.flooddata %}
{% set levels.lvl = levels.lvl + [height.level] %}
{% endfor %}
{{levels.lvl | max }}
json_attributes:
- flooddata
It puts the peak value as the state and puts the entire data set into the sensor attribute “flooddata”:
Not very exciting this time of year … but then the data can be used in many ways. Like for instance I plot it, tabularize it and have automations for flood warnings.
If you get CSV or XML, you should easily be able to pull that apart into things.
Cellar Tracker has got several entrypoints for csv export. One gives a list of wines and quantity, another gives a list of all individual wines, pending wines, consumed wines…
Here is a csv from my “Wine List”
And here is the raw html you get from the entrypoint.
The entrypoint looks like this:
https://www.cellartracker.com/xlquery.asp?table=List&User=USERNAME&Password=PASSWORD&Location=1
Here’s my progress so far:
a) I tried RESTful integration but can’t seem to extract any data (tried various addressing of the XML tree but it’s not returning anything).
Here’s my configuration.yaml code:
rest:
- scan_interval: 900
resource: https://www.cellartracker.com/xlquery.asp?User=User&Password=Pwd&Format=xml
sensor:
- name: "MyWines"
json_attributes_path: "$.cellartracker"
value_template: '{{value_json["cellartracker"]["list"]["row"]["Wine"]["@strValue"]}}'
json_attributes:
- "Wine"
From the logs, I can see that the XML is not converted to JSON:
Anyone knows how to deal with this?
b) tried with multiscraper and I’m able to extract data, but only one value at the time, can’t see to make it work for multiple values, here’s the code:
multiscrape:
- resource: https://www.cellartracker.com/xlquery.asp?User=USER&Password=PWD&Format=xml
scan_interval: 900
parser: lxml
sensor:
- name: "MyWineList"
select: "cellartracker > list > row > Wine"
Again, if anyone can help with this would be highly appreciated.
Unfortunately that is not a CSV file. Either your upload changed it you you opened in Excel or other application and saved it as an Excel spreadsheet.
The message in the image says JSON converted from XML and then shows you the XML. There is likely JSON there. Copy the XML and paste into your message using the insert code tool and we can take that XML and put it on a local server and put together the sensor you need.
I could guess though … the image shows “cellartracker” as the root element … OK.
You have JSON attributes path to that element, could be OK.
But you have then attribute as “Wines”. I do not see any element “Wines” in the image. The child of “cellartracker” is “list”. Change the json_attributes to:
json_attributes:
- "list"
It is not clear to me that you can get/set the path. I did the following for you as a start:
- platform: rest
name: Test Cellartracker
scan_interval: 360000
unique_id: sensor.test_cellartracker
resource_template: http://192.168.1.245:8123/local/Testing/cellartracker.xml
value_template: "{{ now() }}"
json_attributes:
- cellartracker
I created a simple XML and put it in my server at that URL:
<?xml version="1.0" encoding="UTF-8"?>
<cellartracker>
<title>Hello</title>
<copyright>World</copyright>
<list>
<row>
<iWine>3543543</iWine>
<WineBarcode>whekjhw</WineBarcode>
<Quantity>6</Quantity>
</row>
<row>
<iWine>398734987</iWine>
<WineBarcode>wherr34kjhw</WineBarcode>
<Quantity>10</Quantity>
</row>
<row>
<iWine>37364763543543</iWine>
<WineBarcode>whekwereerjhw</WineBarcode>
<Quantity>100</Quantity>
</row>
</list>
</cellartracker>
I Reloaded the REST entities and voila:
Now if you want to eliminate some of that hierarchy, use this:
- platform: rest
name: Test Cellartracker
scan_interval: 360000
unique_id: sensor.test_cellartracker
resource_template: http://192.168.1.245:8123/local/Testing/cellartracker.xml
value_template: "{{ now() }}"
json_attributes_path: "$.cellartracker.list"
json_attributes:
- row
Which will yield this:
Side note: set the scan interval how you wish it to be. I only use a large number for tests because I force update through developer tools. I would also note that this is likely how you would want it. I cannot imagine you want a sensor for every wine. Now you have one sensor that is your collection. You can use custom:flex-table-card now and make a nice tabular view card of your cellar. If you post your real XML and a drawing of what you like, I would be happy to create a flex-table card as I will likely use it.
2 Likes
Yes, seems like OneDrive changed it.
Here is a link with Dropbox. I’ll edit the original post.
Anyway, xml is probably better.
If you want to use my xml-export you can find it in this pastebin:
No problem. Just downloaded and reloaded the sensor.
Or a list of wines:
So if you want a nice tabular view, mock up the headers you want in that table in like Excel and I can replicate it with flex-table-card.
Note, you can just change the sensor above using your URL direct to that website. YOu probably want a different name (I use test when I develop for people). Depending on what type of security, you might do something like this because cellartracker is going to use cookies and would remember you username and password, so you should be able to put them in secrets and pass them to the REST sensor and not have them in the sensor code. Untested but I bet it will work.
- platform: rest
name: Cellartracker Wines
scan_interval: 360000
unique_id: sensor.cellartracker_wines
resource_template: https://www.cellartracker.com/xlquery.asp?Format=xml
username: !secret cellartracker_username
password: !secret cellartracker_password
value_template: "{{ now() }}"
json_attributes_path: "$.cellartracker.list"
json_attributes:
- row