Category: howto

Google Finance: CHSPI broken

Update, 2023-12-15: investing.com rendering is super unstable – I’ve resorted to using ishares.com directly. The new snippet for CHSPI:

=SUBSTITUTE(importxml("https://www.ishares.com/ch/individual/en/products/264107/ishares-spi-ch-fund","//*[@id='fundheaderTabs']/div/div/div/ul/li[1]/span[2]"), "CHF ", "")

Update, 2023-06-19: investing.com has changed some of its html. The new snippet for CHSPI:

=importxml("https://www.investing.com/etfs/ishares-core-spi","//*[@id='__next']/div[2]/div[2]/div/div[1]/div/div[1]/div[3]/div/div[1]/div[1]")

As we all know, Google Finance and ETF (at least ishares) import* in Google Spreadsheets has been broken since December 2022:

https://issuetracker.google.com/issues/262157479

So if you use =GOOGLEFINANCE("CHSPI","price"), in your spreadsheet, all you’ll get is the following error message:

Error:
When evaluating GOOGLEFINANCE,
the query for the symbol: 'CHSPI' returned no data.

Since we all also know, Google Finance isn’t the fastest in fixing bugs (as evidenced by this bug, but also several others prior to this one), and their feeds (latency,..) are best-effort-at-best.

To fix this in your spreadsheet, head over to investing.com, enter whatever stock/currency/etf/.. you want to import data from, copy the url and use that combined with importxml:

Say you’re looking for SWX:CHSPI:

  1. Go to investing.com, enter CHSPI, click on the search result.
  2. This takes you to https://www.investing.com/etfs/ishares-core-spi, where you’ll copy the url ("website address").
  3. Open your spreadsheet, head to the cell you like most, enter:
    =importxml("https://www.investing.com/etfs/ishares-core-spi","//*[@data-test='instrument-price-last']") to get the latest price.
  4. Silently thank investing.com

* Note that not only importing in spreadsheets (using =googlefinance) is broken – none of the symbols show up on Google Finance itself either.

COVID: build out ICUs

Hot take: If ICUs are the bottleneck, build out more ICUs. Pro mode: build out ad-hoc ICUs and train enough people (through rotation) to ensure skilled and experienced staffing in peak times.

You read it here first.

withings-sync on debian buster

Since Withings migrates to OAuth 2.0 by 2021-09, I felt it was time to update the flaky sync solution I had running so far.

Now running https://github.com/jaroslawhartman/withings-sync with debian’s virtualenv:

apt install virtualenv python3-virtualenv
virtualenv -p python3 withings-sync
source withings-sync/bin/activate
pip install withings-sync
export WITHINGS_USER=you
withings-sync

(get token, then set up a regular job to sync)

$ cat sync.sh
#!/bin/bash
source withings-sync/bin/activate
export WITHINGS_USER=you
export GARMIN_USERNAME=you-but-on-garmin
export GARMIN_PASSWORD=your-garmin-password
withings-sync
$ chmod 700 sync.sh
crontab -e
12 4 * * * cronic /home/you/sync.sh

ufw: reminder

apt-get install ufw
ufw app list
ufw allow "WWW Full"
ufw allow "OpenSSH"
ufw enable
ufw status

neat!

setup smtp relay, via relayhost

Wanted to set up a relay, so I can de-/multiplex smtp to different destinations.

Set up A record for mx.destdomain.tld

In receiving dns zone, set up mx record, add spf:

rcptdomain.tld		MX	0 mx.destdomain.tld
rcptdomain.tld		TXT	v=spf1 a mx -all

Configure postfix: configure as relay, and add relayhost to send mail with

sudo apt-get install postfix libsasl2-modules

vi /etc/postfix/main.cf
myhostname = mx.destdomain.tld
mydomain = destdomain.tld
myorigin = destdomain.tld
virtual_alias_domains = rcptdomain.tld
virtual_alias_maps = hash:/etc/postfix/virtual
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous
smtp_sasl_tls_security_options = noanonymous
smtp_tls_security_level = encrypt
header_size_limit = 4096000
relayhost = [smtp.sendgrid.net]:587
inet_protocols = ipv4

vi /etc/postfix/virtual
user@rcptdomain.tld user@gmail.com
postmap /etc/postfix/virtual

vi /etc/postfix/sasl_passwd
[smtp.sendgrid.net]:587 apikey:KEY_GOES_HERE
chmod 600 /etc/postfix/sasl_passwd
postmap /etc/postfix/sasl_passwd

/etc/init.d/postfix restart

sources:

https://jichu4n.com/posts/custom-domain-e-mails-with-postfix-and-gmail-the-missing-tutorial/

https://blog.ruanbekker.com/blog/2018/11/23/setup-a-relayhost-with-postfix-to-send-mail-via-sendgrid/

https://www.spf-record.com/generator

https://www.linode.com/docs/guides/postfix-smtp-debian7/

AORUS XTREME X570 (r1.1): wake from sleep

Finally managed to reliably wake from sleep my new machine running on a r1.1 AORUS X570 board (yeah, I know). Which seemed to be a problem for quite a few of us: https://www.reddit.com/r/gigabyte/comments/geo9bt/x570_aorus_xtreme_failing_wake_from_sleep/

As outlined in that thread, F11 worked just fine – but upgrading to anything beyond that didn’t. Symptoms observed: machine wakes up, tries to spin up for 5 seconds, then powers off.

After some long threads with gigabyte’s support, and trying various configurations, we’ve ended up at the following, which works for me:

  • NVIDIA 451.77
  • Intel Wi-Fi 6 AX200 160MHz 21.90.3.2
  • Intel Wireless Bluetooth 21.80.0.3
  • Aquantia AQtion 2.1.018.0
  • Intel Network Connections 23.5.2.0
  • AMD Chipset Software 2.05.04.352
  • Realtek Audio Driver 6.0.8945.1
  • AORUS X570 BIOS F21

phew, it’s been slighly frustrating 🙂 Gigabyte support was excellent though.

Apple Timemachine, Synology NAS and Error 45

To make sure I remember:

https://www.synology.com/en-us/knowledgebase/DSM/tutorial/Backup/What_should_I_do_to_fix_the_problem_when_connecting_to_Synology_NAS_using_Time_Machine

(I’m getting more and more annoyed about all things Apple, after having to ditch 2 perfectly well working iMac screens because Apple decided to stop supporting Target Display Mode (TDM).)

UniFi Dream Machine: list dhcp leases

tl;dr: /mnt/data/udapi-config/dnsmasq.lease

So I just went on an excursion trying to find all dhcp leases dhcpd on our new UDM had handed out.

Reason: anything behind unmanaged switches behind an AP serving as bridge will not show up in the clients overview in the controller, while being perfectly functional.

Following the official guide to validate static assignments doesn’t work for dream machines – and logging into the USG to run show dhcp leases will simply tell you that show isn’t a command found in its lingo. This is exacerbated by the fact that it’s actually impossible to log in to the UDM as a regular user (ie the user configured through the controller (after enabling advanced features and SSH authentication subsequently)), and you’ll have to login as root with your regular controller password as documented in the getting started guide.

On the brighter side, the controller will show static configurations in the clients dashboard, once you select the “All configured clients” filter.

cat /mnt/data/udapi-config/dnsmasq.lease

To show current dhcp leases.