Best practice parsing wine cellar data

[superjuju1]

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.

[kbrown01]

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.

[kbrown01]

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.

[nedberg (Even Nedberg)]

Yes, seems like OneDrive changed it.

Here is a link with Dropbox. I’ll edit the original post.

Anyway, xml is probably better.

[nedberg (Even Nedberg)]

If you want to use my xml-export you can find it in this pastebin:

cellartracker-xml - Pastebin.com

[kbrown01]

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
  

[nedberg (Even Nedberg)]

Thanks a lot! This worked perfectly. I did not place the username/password in !secrets, but I wil try later.

I managed to get the sensor working and can play with it in the Template Editor.

I tried using a flex-table-card, but could not get it to work. I have used it with the original Cellar Tracker integration and auto-entities, so I have some experience with it, but not much.

I have made a quick mock up in Excel of what I want. If you could point me in the right direction of how I could make a table with the wines in a specific bin, I would really appreciate it. I think I will manage to work out the rest after that.

[kbrown01]

WHat do you want to do with entries that have no Bin? Put them all in one list?

[larvel (Lars Velle)]

Would someone know how to also add “Consumed and Bought last 6 months” like in this picture? https://github.com/ahoernecke/ha_cellar_tracker/blob/019d4b34d65fc095172d71ad3ff24b245c195645/img/dashboard.png

[nedberg (Even Nedberg)]

Yes, a separate list for bottles without a bin will be OK.

[nedberg (Even Nedberg)]

@larvel

Would someone know how to also add “Consumed and Bought last 6 months” like in this picture?

Consumed and purchased are available via another entrypoint. You can change the resource_template in the sensor to use the other entrypoints in new sensors.

Consumed:
https://www.cellartracker.com/xlquery.asp?table=Consumed&User=USERNAME&Password=PASSWORD&Format=xml

Purchases:
https://www.cellartracker.com/xlquery.asp?table=Purchase&User=USERNAME&Password=PASSWORD&Format=xml

[kbrown01]

Breaking that into multiple tables by bin is a too much work I am willing to try. I can easily add bin as a column and sort by bin number but multiple tables for each bin number feels like multiple sensors, one for each bin.

I will look at it a bit but right now I have this:

[kbrown01]

I use decluttering because it is so easy to manipulate things.
Here’s decluttering template:

decluttering_templates:
  cellartracker_settings:
    card:
      type: custom:flex-table-card
      css:
        table+: 'padding: 0px; width: 1600px;'
        tbody tr td:nth-child(1): 'width: 6%;'
        tbody tr td:nth-child(2): 'width: 40%;'
        tbody tr td:nth-child(n+3): 'width: 6%;'
        thead tr th: >-
          background-color: green!important; color:white!important;
          text-align:center; padding-top: 10px; padding-bottom: 10px; font-size:
          18px;
        tbody tr:hover: 'background-color: green!important; color:white!important;'
      card_mod:
        style:
          .: |
            ha-card {
              overflow: auto;
              max-height: 99vh;
              }
          $: |
            .card-header {
               padding-top: 6px!important;
               padding-bottom: 4px!important;
               font-size: 14px!important;
               line-height: 14px!important;
               font-weight: bold!important;
             }
      sort_by: row+
      entities:
        include: sensor.test_cellartracker
      columns:
        - hidden: true
          data: row
          modify: if (x.Bin) {x.Bin} else {'XXX'}
        - name: Bin
          data: row
          modify: if (x.Bin) {x.Bin} else {'XXX'}
        - name: Wine
          data: row
          modify: x.Wine
          align: left
        - name: Vintage
          data: row
          modify: x.Vintage
        - name: CT
          data: row
          modify: if(x.CT) {x.CT} else {'(empty)'}
        - name: Quantity
          data: row
          modify: x.Quantity

And this card:

        type: custom:decluttering-card
        template: cellartracker_settings

Gives you this:

Just to note, those without a bin … the Bin number is ‘XXX’.
To break it out like you want (by bin) means you would need a sensor for every Bin I believe, Then you could repeat the template and use auto-entities to do what you want. I shall leave that up to you as this is your start,

[superjuju1]

many thanks @kbrown01 you’re a star!!

[nedberg (Even Nedberg)]

Thank you! This is fantastic!

There is no problem in listing the wines this way.

I might want to filter out some bottles in different Locations or Bin. I will look into creating separate sensors for each Bin.

[kbrown01]

I have some time today to look again. I think that I did something similar with filtering by league for my Sports statistics. Sports Standings and Scores

It could be buried in that code. Not sure but possibly you can sort and filter.

UPDATE: Yes it can be done and fairly easily, just need to figure out how to wrap things into a loop to make it all generic. But the key is like things for a decluttering template:

decluttering_templates:
  cellartracker_settings:
    card:
      type: custom:flex-table-card
      css:
        table+: 'padding: 0px; width: 1600px;'
        tbody tr td:nth-child(1): 'width: 6%;'
        tbody tr td:nth-child(2): 'width: 40%;'
        tbody tr td:nth-child(n+3): 'width: 6%;'
        thead tr th: >-
          background-color: green!important; color:white!important;
          text-align:center; padding-top: 10px; padding-bottom: 10px; font-size:
          18px;
        tbody tr:hover: 'background-color: green!important; color:white!important;'
      card_mod:
        style:
          .: |
            ha-card {
              overflow: auto;
              max-height: 99vh;
              }
          $: |
            .card-header {
               padding-top: 6px!important;
               padding-bottom: 4px!important;
               font-size: 14px!important;
               line-height: 14px!important;
               font-weight: bold!important;
             }
      sort_by: row+
      strict: true
      entities:
        include: sensor.test_cellartracker
      columns:
        - hidden: true
          data: row
          modify: if (x.Bin == 'B02') {x.Bin} else {null}
        - name: Bin
          data: row
          modify: if (x.Bin == 'B02') {x.Bin} else {null}
        - name: Wine
          data: row
          modify: x.Wine
          align: left
        - name: Vintage
          data: row
          modify: x.Vintage
        - name: CT
          data: row
          modify: if(x.CT) {x.CT} else {'(empty)'}
        - name: Quantity
          data: row
          modify: x.Quantity

This line enforces that the data must exist for all columns.

strict: true

Then changing the modify for each bin (above I used ‘B02’) … you get only the wines in that bin.

Now, if you only had a few bins you could just pass in the target bin as a variable. WOuld be much nicer to wrap the whole thing into something that automatically flexes to as many bins as we have.

But it is now mostly solved. I can move Bin to the title.

I will need to make some small modification to handle the no bin situation.

Update: I think you may need to enter something in Bin number in your collections. Trying to analyze what to do and sort it out based on unique bin numbers is not going to work.

I modified it to this but it does not support no Bin number

And now begining to love it …

I have implemented a way to sort on any column. In my opinion, that should remove the “Bin” separation and put Bin into a column that is sortable. Then you could sort by any column and maybe if we do it right, multiple columns (not sure). But bin could also be one of them. If you look at the picture above, it is sorted by Wine name within each bin.

I have 1000 bottle in house cellar and 10,000 bottle under house. I tend to say to myself …

Country → Varietal → maybe region … and then I want to know the bin to find it. Not a list isolated by bins for sure.

I am going to continue on building that, But next post I will give you handles the by bin tables. That will be removed in my final product.

[kbrown01]

Final “by Bin” view which does not include those with no “bin”. I am not continuing with this but it is here for your pleasure and use.

decluttering_templates:
  cellartracker_settings:
    card:
      type: custom:flex-table-card
      title: '[[bin]]'
      css:
        table+: 'padding: 0px; width: 100%'
        thead tr th:nth-child(1): 'width: 200px;'
        thead tr th:nth-child(n+2): 'width: 30px;'
        thead tr th: >-
          background-color: green!important; color:white!important;
          text-align:center; padding-top: 10px; padding-bottom: 10px; font-size:
          18px;
        tbody tr:hover: 'background-color: green!important; color:white!important;'
      card_mod:
        style:
          .: |
            ha-card {
              overflow: auto;
              }
          $: |
            .card-header {
               padding-top: 6px!important;
               padding-bottom: 4px!important;
               font-size: 30px!important;
               font-weight: bold!important;
               text-align: center;
             }
      strict: true
      sort_by: row+
      entities:
        include: sensor.test_cellartracker
      columns:
        - hidden: true
          data: row
          modify: x.[[sort]]
        - hidden: true
          data: row
          modify: if (x.Bin == '[[bin]]') {x.Bin} else {null}
        - name: Wine
          data: row
          modify: x.Wine
          align: left
        - name: Vintage
          data: row
          modify: x.Vintage
        - name: Varietal
          data: row
          modify: x.Varietal
        - name: Country
          data: row
          modify: x.Country
        - name: Region
          data: row
          modify: x.Region
        - name: Producer
          data: row
          modify: x.Producer
        - name: CT
          data: row
          modify: if(x.CT) {x.CT} else {'(empty)'}
        - name: Quantity
          data: row
          modify: x.Quantity
views:
  - title: Panel Playground
    icon: mdi:toy-brick-marker
    type: panel
    badges: []
    cards:
      - type: vertical-stack
        cards:
          - type: custom:auto-entities
            card:
              type: vertical-stack
            card_param: cards
            filter:
              template: >
                {% set bins = namespace(bin=[]) %} {% for row in

                state_attr('sensor.test_cellartracker', 'row') %}
                  {% if row.Bin %}
                    {% set bins.bin = bins.bin + [row.Bin] %}
                  {% else %}
                    {% set bins.bin = bins.bin + ['XXX'] %}
                  {% endif %}
                {% endfor %} {% set ubins = bins.bin | unique | sort | list %}
                {%- for bin in ubins -%} 
                    {{ {"type": "custom:decluttering-card",
                        "template": "cellartracker_settings",  "variables": [{"bin": bin}, {"sort": "Wine"}]}
                    }}, 
                {%- endfor -%}
title: Panel Playground

[kbrown01]

I have been attempting to integrate a method for dynamically sorting the content in the flex-table-card and cannot see how it can be done. I can do it for one column by hand editing the YAML but cannot see how to be able to set that from the state of another thing like an input_select. Even worse, I see no way to sort_by multiple columns when those columns come from the attributes of a single entity.

Therefore, I will need to rethink this and attempt something new (possibly markdown for the tables with some additional JS for sorting).

[nedberg (Even Nedberg)]

@kbrown01

Final “by Bin” view which does not include those with no “bin”. I am not continuing with this but it is here for your pleasure and use.

Thank you @kbrown01 ! This is working just fine. I added some stuff to modify under Vintage to change 1001 into N.V (non vintage) and rounded CT score to one decimal.

 - name: Vintage
    data: row
    modify: >-
      if(parseFloat(x.Vintage).toFixed(0) == 1001) {"N.V."}
      else{parseFloat(x.Vintage).toFixed(0)}
- name: CT
    data: row
    modify: >-
      if(x.CT) { parseFloat((x.CT).replace(',', '.')).toFixed(1)} else
      {'(empty)'}

I agree with you that sorting by bin is not ideal. And a way to sort/filter on producer, varietal, type or country would be a great addition!

Can I buy you a coffee/beer/glass of wine?

[kbrown01]

www.riocrest.com … I own that winery … and harvest is calling for the next several weeks so I doubt there will be much I can do. Come for a visit next time you are in Russian River/Sonoma