openpyxl - data_only beware

openpyxl is a popular library to access Excel data via python.

However, there is an unexpected side effect if you have formulas in your Excel workbook.
I'm not the first to find that out the hard way - so here is another warning.

If you have a formula in your xlsx-spreadsheet like "=B1-A1" the spreadsheet app (Excel, LibreOffice etc.) stores the formula as well as the calculated result which you can see in the cell.

If the formula is stored e.g. in "C1", and you open the file in your python script like this:

    import openpyxl

    wb = openpyxl.load_workbook(filename="testwb.xlsx")
    print(sheet["C1"].value)
    # prints the formula

The result will be the formula, not the calculated value.

In order to access the calculated value, you have to open it with data_only set to True.

    import openpyxl

    wb = openpyxl.load_workbook(filename="testwb.xlsx", data_only=True)
    print(sheet["C1"].value)
    # prints the calculated value

The catch is that if you try to save the workbook later with for example

    wb.save("testwb2.xlsx")

all formulas in the entire workbook are gone (if the workbook was loaded with data_only=True).

If you must have access to the results of the formulas that Excel has calculated, the work-around is to open two instances of the workbook: one with and one without data_only.
Make the one using data only also read_only, just in case

    wb = openpyxl.load_workbook(filename="testwb.xlsx", data_only=False)
    wb_dataonly = openpyxl.load_workbook(filename="testwb.xlsx", data_only=True, read_only=True)

This way you have access to the calculated values via wb_dataonly and you can add data and save the result using wb... and yes, you have to keep in mind that the two instances go out of sync as soon as you modify wb.

Bitwarden's ssh-agent

 

The most secure way of accessing external ssh servers is the use of ssh-keys and I'm deploying them regularly. The private key is stored on my hard drive, and I'm protecting it with a passphrase. Remembering the passphrase especially for a site you rarely use has always been a PITA.

Since the beginning of this year (2025) the password manager Bitwarden allows you to manage your ssh-keys as well. So I gave it a try.

Under Ubuntu you have to install the Bitwarden desktop app from the snap repo and connect it to your vault. After setting SSH_AUTH_SOCK in your .bashrc the desktop app acts as your ssh-agent:

export SSH_AUTH_SOCK=/home/your_user_name/snap/bitwarden/current/.bitwarden-ssh-agent.sock

While importing keys I've noticed that Bitwarden only likes the "new ones" (that use Ed25519 elliptic curve crypto).  It was a good opportunity to re-key.

Bitwarden itself always generates Ed25519 keys.  The reason might be that this special kind of key allows you to calculate the public key from the private one.

When importing keys into Bitwarden I had to provide the passphrase to my keys, which made me hesitate - perhaps a Gibson-ian reaction :) - because it meant that the key is stored "naked" in the Bitwarden vault.
I would have liked it more if Bitwarden would have provided the passphrase on request - but this would have made integration as ssh-agent impossible.

How is the risk mitigated?

• You are prompted each time a ssh-key is requested from the vault, which is an improvement over the regular ssh-agent.
• There is no indication where a key can be used, if you don't put it into the comment.
• Up until now Bitwarden has a spotless record of securing your vault.

Integration with .ssh/config

The .ssh/config file allows you to configure additional items like hostname, user name, port, the ssh-key AKA the identity file, and port forwarding rules for a given host.  This way you don't have to specify them every time in a ssh command.  If and only if an identity file is configured for a given host a ssh-agent will be queried.

If you generate the ssh-key within Bitwarden the private key is stored in your vault.  But what do you put into the IdentityFile field to make the system query the Bitwarden app?

As Kiko Piris pointed out here, it needn’t be the private key that is stored on the hard drive.  It might also be the public key.  This will not help you if the Bitwarden app is not running, but at least it will make ssh try to contact the ssh-agent.

You might have noticed during the regular use of the Bitwarden app that the IdentityFile field has to be present in the .ssh/config file but the key file itself is not used.
I still have my passphrase-protected private keys on my hard drive with the IdentityFile field pointing to them.  But when I log in with ssh I’m not queried for the passphrase, instead the Bitwarden Desktop app pops up requesting confirmation to use the key it has stored in its vault.

The Bitwarden app has a button that copies the public key into the clipboard which can be used to create the public key file which then can be specified in the IdentityFile field.

It is - as usual - a compromise between security and convenience.  If it fits your risk profile it's a nice tool.

matplotlib - The secret of the vanishing x-ticks

The versions:
* Ubuntu 24.04
* python 3.12.3
* matplotlib 3.10.0

I've searched for this solution for days. So I describe it here for anyone who might need ist.

The goal is rather simple:

I want to create a figure with three subplots, each with an independent x-axis because I want to display data with different time periods.

I expected to get something like this:

Three separate subplots, each with its own labels on the x-axis showing a grid as well as date and time.

And that is exactly what you get if execute this simple program.

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import matplotlib.dates as mdates
import matplotlib.pyplot as plt
from datetime import datetime

import matplotlib as mp
print(mp.__version__)
# 3.10.0

FORMAT_MAJOR = False
FORMAT_MINOR = False

# Format definitions
# not all are used

years = mdates.YearLocator()            # every year
months = mdates.MonthLocator()          # every month
days = mdates.DayLocator()              # every day
hours = mdates.HourLocator()            # every hour
years_fmt = mdates.DateFormatter('%Y')
month_fmt = mdates.DateFormatter('%m')
day_fmt = mdates.DateFormatter('%d')
hour_fmt = mdates.DateFormatter('%H')

fig, axs = plt.subplots(nrows=3, ncols=1, figsize=(187, 12), sharex="none")

datx0 = [ datetime(2025, 1, 31), datetime(2025, 2, 2), datetime(2025, 2, 3) ]
daty0 = [100, 200, 150]

datx1 = [ datetime(2025, 2, 4), datetime(2025, 2, 5), datetime(2025, 2, 7) ]
daty1 = [150, 100, 150]

datx2 = [ datetime(2025, 2, 1), datetime(2025, 2, 4), datetime(2025, 2, 5) ]
daty2 = [200, 200, 150]

axs[0].plot(datx0, daty0)
axs[1].plot(datx1, daty1)
axs[2].plot(datx2, daty2)

for pos in range(3):  # 0..2
    curraxs = axs[pos]
    curraxs.grid(True)

    if FORMAT_MAJOR:
        curraxs.xaxis.set_major_locator(days)
        curraxs.xaxis.set_major_formatter(day_fmt)
        curraxs.tick_params(axis="x", which="major", rotation=45)

    if FORMAT_MINOR:
        curraxs.xaxis.set_minor_locator(hours)
        curraxs.xaxis.set_minor_formatter(hour_fmt)
        curraxs.tick_params(axis="x", which="minor", rotation=90)

    # only 1% "slack" at each end
    curraxs.set_xmargin(0.01)

print(axs[0].xaxis.get_majorticklabels())
print(axs[1].xaxis.get_majorticklabels())
print(axs[2].xaxis.get_majorticklabels())

plt.show()

As you can see, there are three data series.

• The first from 2025-1-31 to 2025-2-3.
• The second from 2025-2-4 to 2025-2-7.
• The third from 2025-2-1 to 2025-2-5.

The date ranges have been chosen to overlap slightly.  The y-data has no special meaning other than to show different graphs in the subplots.

The vanishing act occurs if you try to format the x-axis labels.

This is usually done with:


import matplotlib.dates as mdates

days = mdates.DayLocator()
day_fmt = mdates.DateFormatter('%d')

axs.xaxis.set_major_locator(days)
axs.xaxis.set_major_formatter(day_fmt)

This works fine for a single axis.  If you have more than one, strange things happen:

That’s the output with the variable FORMAT_MAJOR set to True.

The missing x-ticks become more apparent if you set FORMAT_MINOR to True as well.

• In the first subplot the ticks for 2025-01-31 are missing.
• In the second subplot the ticks from 2025-02-05 and above are missing.
• Only the third subplot has all x-ticks.

The output of the get_majorticklabels() of the three axis at the end of the program...

print(axs[0].xaxis.get_majorticklabels())
print(axs[1].xaxis.get_majorticklabels())
print(axs[2].xaxis.get_majorticklabels())

...gives an indication of what happened:

They are all identical – using the values from the last call 2025-01-01 to 2025-02-05.
Which explains the missing parts at the beginning of the first subplot and the missing days at the end of the second.

So, how to fix this?

It seems that – contrary to what one might believe – the xxxxLocator() calls are not simply generators that produce ticks as requested.  They seem to keep some kind of internal state – in this case of the last subplot – influencing all the other uses.

You have to move them into the for-loop so that for each axis a “new” xxxxLocator() is created.

...

for pos in range(3):  # 0..2
    curraxs = axs[pos]
    curraxs.grid(True)

    days = mdates.DayLocator()
    hours = mdates.HourLocator()

    if FORMAT_MAJOR:
        curraxs.xaxis.set_major_locator(days)
        curraxs.xaxis.set_major_formatter(day_fmt)
        curraxs.tick_params(axis="x", which="major", rotation=45)
        
    ...

This gives the expected result: