Excel : changer les couleurs des courbes de tous les graphiques en meme temps

Written by pmd - - no comments

Voici une macro VBA qui va modifier tous les graphiques de la feuille active uniquement, en appliquant une couleur spécifique à chaque série (courbe) dans chaque graphique de la feuille active.

Code VBA à utiliser :

  • Appuie sur Alt + F11 pour ouvrir l’éditeur VBA.
  • Clique sur Insertion > Module.
  • Colle ce code :
Sub ColorerCourbesGraphiques()
    Dim ch As ChartObject
    Dim s As Series

    For Each ch In ActiveSheet.ChartObjects
        With ch.Chart
            If .SeriesCollection.Count >= 1 Then
               .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(128, 128, 128) ' Gris
            End If
            If .SeriesCollection.Count >= 2 Then
                .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 112, 192) ' Bleu
            End If
            If .SeriesCollection.Count >= 3 Then
                .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(255, 0, 0) ' Rouge
            End If
        End With
    Next ch

    MsgBox "Couleurs mises à jour pour tous les graphiques de la feuille active."
End Sub
 

Important :

  • Cette macro ne modifie que les graphiques de la feuille active.
  • Si tu veux l’appliquer à une autre feuille, sélectionne-la avant d’exécuter la macro.
  • Tu peux ajuster les couleurs en modifiant les valeurs RGB.

 

Classified in : Office - Tags : none

OpenWRT on WNR3500L + E3272 in NCM

Written by pmd - - no comments

⚫ Installed OpenWRT on WNR3500L

login as: root
root@192.168.1.1's password:

BusyBox v1.35.0 (2023-04-27 20:28:15 UTC) built-in shell (ash)
  _______                     ________        __
 |       |.-----.-----.-----.|  |  |  |.----.|  |_
 |   -   ||  _  |  -__|     ||  |  |  ||   _||   _|
 |_______||   __|_____|__|__||________||__|  |____|
          |__| W I R E L E S S   F R E E D O M
 -----------------------------------------------------
 OpenWrt 22.03.5, r20134-5f15225c1e
 -----------------------------------------------------
root@OpenWrt:~#

[OpenWrt Wiki] NETGEAR WNR3500U/WNR3500L
I wanted to install 24.10.0 but it was impossible to connect to a wifi and install all necessary packets.
I installed 22.03.5.

⚫ Expected configuration

One OpenWRT router getting internet access through a 4G USB dongle using NCM connection (much faster than PPP).

⚫ Preparing

Update package list:

# opkg update

Install necessary packets to manage e3272 huawei 4g usb stick:

# opkg install kmod-usb2 kmod-usb3 kmod-usb-net-huawei-cdc-ncm comgt-ncm kmod-usb-serial kmod-usb-serial-option kmod-usb-serial-wwan luci-proto-ncm usb-modeswitch kmod-usb-serial-ipw

Install package to have wifi N available:

# opkg install kmod-brcmsmac

Install some tools for editing files and communicate with 4g usb stick:

# opkg install nano picocom

Activate USB port power supply:

# nvram set board_id=U12H136T99_NETGEAR
# nvram set boardflags=0x00000710
# nvram commit

Remove b43 wifi driver whci does not enable wifi N:

# opkg remove kmod-b43
# rm /etc/modules.d/b43 (probably unecessary)

Here switch OFF the router, and switch ON.

Router will be using brcmsmac wifi driver so you can use wifi N (WNR3500L v1 woking perfectly but wifi in Wireless-G only). 
For information there is another propriatory driver available which might have better performances but I didn't get it working (broadcom-wl). It takes too much space (I used this script to free the space when opkg couln't install fully)

Router is ready to be configured.

⚫ Configuration of e3272 stick

Disconnect from previously connected wifi.

See some information about the USB 4g stick e3272 here: OpenWRT on GL-AR750 + E3372 in NCM + receive SMS (2023) - pmd

Go to Network > Interfaces and create Add new interface... with following parameters:

General settings:
 | Protocol => NCM
 | Bring up on boot => YES
 | Modem device => /dev/cdc-wdm0
 | Network Mode => LTE
 | IP Protocol => IPv4
 | APN => mmsbouygtel.com
Firewall setting:
 |  Assign firewall-zone as => wan

Save and apply and after this router should be connected to internet (check modem led not blinking anymore).

For information this is how to discuss with modem with AT commands continuously or in one line for shell script:

root@OpenWrt:~# picocom -b 115200 /dev/ttyUSB1
picocom v3.1

port is        : /dev/ttyUSB1
flowcontrol    : none
baudrate is    : 115200
parity is      : none
databits are   : 8
stopbits are   : 1
escape is      : C-a
local echo is  : no
noinit is      : no
noreset is     : no
hangup is      : no
nolock is      : no
send_cmd is    : sz -vv
receive_cmd is : rz -vv -E
imap is        :
omap is        :
emap is        : crcrlf,delbs,
logfile is     : none
initstring     : none
exit_after is  : not set
exit is        : no

!! Settings mismatch !! Type [C-a] [C-v] to see actual port settings
Type [C-a] [C-h] to see available commands
Terminal ready
AT
OK
AT+CSQ
+CSQ: 11,99


OK

ERROR
AT^SETPORT?
^SETPORT:FF;10,12,16,A1,A2

OK
   <<<<<<<<<<<<<<<<<< here I did CTRL+A then CTRL+X
Terminating...
Thanks for using picocom
root@OpenWrt:~#

root@OpenWrt:~# echo -e "AT+CSQ\r" | picocom -b 115200 -q --exit-after 2000 /dev/ttyUSB1
AT+CSQ
+CSQ: 10,99

OK
root@OpenWrt:~#

⚫ Get APN in command line

root@OpenWrt:~# uci get network.wwan4g.apn
mmsbouygtel.com
root@OpenWrt:~#

⚫ Find best place for USB 4g stick

Once the modem is connected, you can use this file to monitor signal strength to find the best place for USB 4g stick.

root@OpenWrt:~# nano /root/csq_monitor2.sh
#!/bin/sh
DEVICE="/dev/ttyUSB1"  # update as necessary
if [ ! -e "$DEVICE" ]; then
  echo "Erreur : $DEVICE introuvable"
  exit 1
fi

echo "Lecture du signal AT+CSQ via $DEVICE (Ctrl+C pour arrêter)..."

while true; do
  # On lit toute la sortie de picocom
  RAW_OUTPUT=$(echo -e "AT+CSQ\r" | picocom -b 115200 -q --exit-after 2000 "$DEVICE" 2>/dev/null)

  # On filtre uniquement la ligne avec +CSQ
  RESPONSE=$(echo "$RAW_OUTPUT" | grep "+CSQ:" | tr -d '\r')

  if [ -n "$RESPONSE" ]; then
    echo -n "$(date +%H:%M:%S) -> $RESPONSE"
    VALUE=$(echo "$RESPONSE" | cut -d ':' -f2 | cut -d ',' -f1 | tr -d ' ')
    case "$VALUE" in
      [0-9])         QUAL=" (Très faible)" ;;
      1[0-4])        QUAL=" (Moyen)" ;;
      1[5-9])        QUAL=" (Bon)" ;;
      2[0-9]|3[0-1]) QUAL=" (Excellent)" ;;
      99)            QUAL=" (Inconnu)" ;;
      *)             QUAL=" (?)" ;;
    esac
    echo "$QUAL"
  else
    echo "$(date +%H:%M:%S) -> Pas de réponse"
    sleep 1
  fi

  #sleep 1
done
root@OpenWrt:~# chmod +x /root/csq_monitor2.sh
root@OpenWrt:~# /root/csq_monitor2.sh
Lecture du signal AT+CSQ via /dev/ttyUSB1 (Ctrl+C pour arrêter)...
19:23:22 -> +CSQ: 11,99 (Moyen)
19:23:26 -> +CSQ: 11,99 (Moyen)
19:23:29 -> +CSQ: 11,99 (Moyen)
19:23:33 -> +CSQ: 11,99 (Moyen)
19:23:36 -> +CSQ: 11,99 (Moyen)
19:23:40 -> +CSQ: 11,99 (Moyen)
19:23:43 -> +CSQ: 11,99 (Moyen)
19:23:47 -> +CSQ: 11,99 (Moyen)
19:23:51 -> +CSQ: 11,99 (Moyen)
^CTerminated
root@OpenWrt:~#

 

⚫ Fix modem disconnection automatically 

Inspiration scripts from here: https://openwrt.org/docs/guide-user/network/wan/wwan/ethernetoverusb_ncm

Create a shell script that will ping an adress every minute to check connectivity status:

# nano /root/connectivityCheck.sh
----------
#!/bin/sh

# Enter the FQDNs you want to check with ping (space separated)
# Script does nothing if any tries to any FQDN succeeds
FQDN="www.google.com"
FQDN="$FQDN www.amd.com"
FQDN="$FQDN www.juniper.net"

# Sleep between ping checks of a FQDN (seconds between pings)
SLEEP=3                         # Sleep time between each retry
RETRY=3                         # Retry each FQDN $RETRY times
SLEEP_MAIN=60                   # Main loop sleep time
SLEEP_RESTSTART_NETWORK=240     # Waiting time before retrying after an interface reset

check_connection()
{
  for NAME in $FQDN; do
    for i in $(seq 1 $RETRY); do
      ping -c 1 $NAME > /dev/null 2>&1
      if [ $? -eq 0 ]; then
        #echo "ping $NAME OK"
        #echo "ping $NAME OK" | logger -t "connectivityCheck.sh[$$]" -p info
        return 0
      fi
      #echo "ping $NAME NOK"
      echo "ping $NAME NOK" | logger -t "connectivityCheck.sh[$$]" -p info
      sleep $SLEEP
    done
  done
  # If we are here, it means all failed
  return 1
}

echo "Starting connectivityCheck.sh" | logger -t "connectivityCheck.sh[$$]" -p info

while true; do
  check_connection
  if [ $? -ne 0 ]; then
    echo "All ping for connectivity check failed... Trying reconnecting..." | logger -t "connectivityCheck.sh[$$]" -p info
    /etc/init.d/ncm-network start
    sleep $SLEEP_RESTSTART_NETWORK
  else
    sleep $SLEEP_MAIN
  fi
done

As you see, above script will be calling /etc/init.d/ncm-network if connectivity is lost.
Let's create it:

# nano /etc/init.d/ncm-network
----------
#!/bin/sh /etc/rc.common

# Interface to send AT commands
DEVICE='/dev/ttyUSB1'
# Interface name from /etc/config/network
IFNAME='wwan4g'
# Your APN:
APN=$(uci get network.wwan4g.apn)

START=70
STOP=90

start() {
        if [ -e ${DEVICE} ]; then
                ifdown $IFNAME
                sleep 3
                echo -n "1-1" > /sys/bus/usb/drivers/usb/unbind
                sleep 3 # waiting for USB disconnection
                echo -n "1-1" > /sys/bus/usb/drivers/usb/bind
                sleep 10 # waiting for USB connection
                echo -ne "AT^NDISDUP=1,0\r\n" > ${DEVICE}
                echo "AT^NDISDUP=1,0 > ${DEVICE}" | logger -t "ncm-network[$$]" -p info
                sleep 3 # wait for disconnect
                echo -ne "AT+CGATT=0\r\n" > ${DEVICE}
                echo "AT+CGATT=0 > ${DEVICE}" | logger -t "ncm-network[$$]" -p info
                sleep 3 # wait for detach from network
                echo -ne "AT+CFUN=0\r\n" > ${DEVICE}
                echo "AT+CFUN=0 > ${DEVICE}" | logger -t "ncm-network[$$]" -p info
                sleep 10 # wait for activate plane mode
                echo -ne "AT+CFUN=1\r\n" > ${DEVICE}
                echo "AT+CFUN=1 > ${DEVICE}" | logger -t "ncm-network[$$]" -p info
                sleep 10 # wait for deactivate plane mode (activate radio)
                #echo -ne "AT^NDISDUP=1,1,\"${APN}\"\r\n" > ${DEVICE}
                #echo "AT^NDISDUP=1,1,\"${APN}\" > ${DEVICE}" | logger -t "ncm-network[$$]" -p info
                #sleep 3 # wait for connect
                ifup $IFNAME
                echo "/etc/init.d/ncm-network start ${DEVICE} executed" | logger -t "ncm-network[$$]" -p info
        else
                echo "No such device ${DEVICE}" | logger -t "ncm-network[$$]" -p info
        fi
}

stop() {
        if [ -e ${DEVICE} ]; then
                ifdown $IFNAME
                sleep 3
                echo -ne "AT^NDISDUP=1,0\r\n" > ${DEVICE}
                echo "AT^NDISDUP=1,0 > ${DEVICE}" | logger -t "ncm-network[$$]" -p info
                echo "/etc/init.d/ncm-network stop ${DEVICE} executed" | logger -t "ncm-network[$$]" -p info
        else
                echo "No such device ${DEVICE}" | logger -t "ncm-network[$$]" -p info
        fi
}

Above script will be restarting the network if connectivity loss is confirmed by /root/connectivityCheck.sh.

To have /root/connectivityCheck.sh monitoring connectivity status, you need to start it at rooter startup. In luci interface, go to System > Startup > Local startup and add this lines before exit 0:

sleep 180 # waiting for router to be started for sure
/root/connectivityCheck.sh &

exit 0

⚫ Identify Wi-Fi connection as metered automatically

Identify Wi-Fi connection as metered on Android automatically
Extract from /etc/config/dhcp:

config dhcp 'lan'
        option interface 'lan'
        option start '100'
        option limit '150'
        option leasetime '12h'
        option dhcpv4 'server'
        list dhcp_option '43,ANDROID_METERED'


Identify Wi-Fi connection as metered on Windows automatically (see next link to have it working on Windows AND iOS)
Identify Wi-Fi connection as metered on Windows and iOS automatically (see section Mimicking an iOS hotspot)
Extract from /etc/config/wireless (so you need to apply it on all wifi Access Point):

config wifi-device 'radio0'
        option type 'mac80211'
        option path 'bcm47xx_soc/bcma0:1'
        option band '2g'
        option cell_density '0'
        option htmode 'HT20'
        option channel '11'
        option legacy_rates '1'
        option txpower '19'
        list hostapd_options 'vendor_elements=DD0A0017F206010103010000'

 

Meubles

Written by pmd - - no comments

Meuble d'entrée

10 planches + pieds ou base de meuble non précisée ici :

    Largeur (cm) Hauteur (cm) Profondeur (cm)
le porte chapeau 105 1.5 30
l'assise 105 1.5 43.5
2x triangle 1.5 30 30
3x support assise 1.5 31.5 41.5
contre mur 105 220 1.5
bas meuble 105 1.5 43
porte 105 33 1.7


Classified in : DIY - Tags : none

Travaux appartement

Written by pmd - - no comments

Quelques réparations Janvier 2025

Porte de la chambre :

Le montant de la porte, du côté charnière, était chancelant. La porte ne fermait plus. Manifestement l'artisant n'a pas mis beaucoup de mousse...

Achat/utilisation de quelques outils et consommables:
Visseuse perceuse Bosh ☑️
Meche bois 8☑️
Perceuse à percussion☑️
Longue meche beton 8☑️
Longues vis + chevilles☑️
Fraise conique pour pourvoir cacher la vis☑️
Mousse pour porte? ❌ car les vis ont été préférées.
Colle a bois☑️
Platre ☑️
Peinture blanche ☑️
Pinceau ☑️

Renforcement avec une grosse vis de 8x120mm au niveau de chaque charnière. Ensuite comblage au platre, ponçage, peinture. Collage des caches côté chambre.

 

Armoire de la chambre :

La porte de gauche ne ferme plus. Les charnières des portes de gauche et centrale sont tordues.

Achat/utilisation de quelques outils et consommables:
Visseuse perceuse Bosh ☑️
Meche bois 5 ☑️
6x charnières extra grande ouverture ☑️

Réalisation d'un maquette pour créer un chablon (pas de photo) afin de savoir ou percer.

 

Radiateur :

Bizarre le radiateur est très sale comme s'il venait d'un chantier. Trace d'enduit dans les ailettes... Il y a une fuite en plein milieu entre deux éléments.

Achat/utilisation de quelques outils et consommables:
Ne pas oublier clé 6 pans pour fermer le T de réglage ☑️
Tournevis plat pour dépressuriser le radiateur ☑️
Chiffons + Papier de verre + Loctite 7235 ☑️
Fluorosilicone 16x1cm et 20x1cm ☑️
2x colliers de serrage métallique >16cm ☑️
Холодная сварка si la technique avec fluorosilicone ne fonctionne pas ☑️ non utilisé

Technique du sac plastique pour devier la fuite en attandant de réparer.
Circulation d'eau coupée aux deux té puis dépressurisation.
Nettoyage au tournevis pour enlever le plus gros.
Puis nettoyage avec une chaussette sèche, puis imbibée de Loctite 7235.
Installation du fluorosilicone puis serrage avec le collier métallique.
Remise en service.

 

Porte entrée :

On peut normallement fermer la porte depuis l'exterieur. Mais impossible de fermer à clé depuis l'interieur. Et le loquet une fois fermé est parfois très dur à ouvrir donc... enfermé à l'intérieur.

Achat/utilisation de quelques outils et consommables:
Huile pour le loquet ☑️

Le loquet a été sorti de la porte. Le mécanisme a été nettoyé et huilé. Nikel.
Les caches serrures ont été retirés et réajustés.

 

Salle de bain :

Achat/utilisation de quelques outils et consommables:
Vis pour tiroir ☑️

La planche du tiroir ne tenait plus très bien. Scotch double face retiré puis refixée avec 5 vis.
Nettoyage de la vitre de douche avec mélange grand-mère.
Le tuyau d'évacuation d'eau de la machine à laver fuyait. Le tuyau d'évacuation a été enfoncé plus profondément.
Ajustement chasse d'eau car il fallait maintenir le bonton appuyé pour faire couler l'eau. Deux petites pièces orange ont été retirées qui empechait d'enfoncer suffisamment le bouton pour déclencher et automaintenir la chasse d'eau.

Classified in : DIY - Tags : none

Updating RAM on 8530w laptop

Written by pmd - - no comments

Solved: add memory - HP Support Community - 5788315
=> 2x4GB maximum

HP EliteBook 8530w RAM Replacement - Tutoriel de réparation iFixit

Memoria Ram DDR2 2GB 4GB PC2-6400S 800MHz PC2-5300S 667MHZ 200PIN 1.8V Non-ECC SO-DIMM Desktop Laptop Memory CL5 Dual Channel | AliExpress
Memoria Ram DDR2 2GB 4GB PC2-6400S 800MHz PC2-5300S 667MHZ 200PIN 1.8V Non-ECC SO-DIMM Desktop Laptop Memory CL5 Dual Channel | AliExpress
=> Bought 2x4GB PC2-6400 800MHz SODIMM for about 37 USD.
=> Will update from 2x2GB to 2x4GB when mounted.

But the improvement from 4GB to 8GB is actually disapointing. Not a big step to a faster laptop like the SSD was.

10 minutes options trading and ETF investing

Written by pmd - - no comments

10-Minute Options Trading and ETF Investing: Rapidly Build Wealth, Retire Early, and Live Free from the Worry of Market Crashes
Written by TRAVIS WILKERSON.

Knowing about options likely help reading this book. At least it will greatly help to put the presented strategy into action.

See previous books I read to learn about options:

  1. Get Rich with Options: Four Winning Strategies Straight from the Exchange Floor
  2. Create Your Own Hedge Fund: Increase Profits and Reduce Risk with ETFs and Options

He is presented two ways to manage a portfolio. One way to reach a 6-figures account (using ETF + LEAP option CALL), and a way to keep account growing in a slower way but taking emotions into account (using ETF + LEAP option CALL + LEAP option PUT).

Buy-and-Old is a very good startegy but there is a way to enhance it safely enough.

Travis tells to start the portfolio at any day of the year then to balance the portfolio every January.

He is using LEAP options (Long-term Equity Anticipation Securities (LEAPS) are a type of stock or index option with notably longer expiration dates as compared to standard options) expiring in December.

🔵 Managing the portfolio up to 100k

He is using SPY ETF + buying At The Money CALL option (to boost profit... in a bull market ⚠).

The allocation is the following :

  • 80% to buy SPY
  • 20% to buy SPY CALL ATM

As of today 12/09/2024 :

  • The furthest available SPY option is at expiration 18/12/2026.
  • CALL at 555 strike cost 74.65 USD so one CALL would cost 7465 USD.
    • 20% of the portfolio is 10000 x 0.2 = 2000 USD
    • SPY seems too big to trade options for a small account.
    • An account of 37325 USD would buy 1 CALL.
  • SPY is trading at 555.29 USD => (37325 - 7465) / 555.29 = 53.8 shares to buy.

To be review in January 2025...

🔵 Managing the portfolio above 100k

To become richer up to 100k, you could invest agressively. But to stay rich you need to invest conservatively. 

He is using SPY ETF + buying At The Money CALL option (to boost profit... in a bull market ⚠) + buying At The Money PUT option (as an insurance against losses and emotions).

Here is what you buy when you follow the enhanced buy & hold blueprint:

  1. SPY broad-based index fund in buy-and-old mode for safe and stable returns
  2. PUT option as an insurance against the stock market
  3. CALL option to pay for the PUT and to boost the returns

How you allocate?

One options controls 100 shares. So you would buy:

  • 1 PUT and 1 CALL if you own between 0 and 150 shares of SPY
  • 2 PUT and 2 CALL if you own between 151 and 250 shares of SPY
  • 3 PUT and 3 CALL if you own between 251 and 350 shares of SPY
  • etc...

As of today 12/09/2024 :

  • The furthest available SPY option is at expiration 18/12/2026.
  • CALL at 555 strike cost 74.65 USD so one CALL would cost 7465 USD.
  • PUT at 555 strike cost 44.75 USD so one PUT would cost 4475 USD.
    • 7465 + 4475 = 11940 USD
  • SPY is trading at 555.29 USD => (100000 - 11940) / 555.29 = 158.6 shares to own.

158 shares of SPY makes it 87735 USD or 87.7% of the portfolio.
1 PUT + 1 CALL makes it 11940 USD or 11.9 % of the portfolio.
The remaining money (324.18 USD) stay in cash (or less to pay the trading fees 😉).

To be review in January 2025...

 

Edit 25/11/2024:
P/L => (10859-7465)+(2927-4475)+158*(595.51-555.29) = +8200.76 USD

Edit18/01/2025:
P/L => ((101.72-74.65)+(30.10-44.75))*100+(597.58-555.29)*158 = + 7923.82 USD
Value after selling put and call => ((101.72)+(30.10))*100+(597.58)*158 = +107599.64 USD + 324.18 USD

As of today 18/01/2025 :

  • The furthest available SPY option is at expiration 17/12/2027.
  • CALL at 595 strike cost 106.66 USD so one CALL would cost 10666 USD.
  • PUT at 595 strike cost 50.0 USD so one PUT would cost 5000 USD.
    • 10666 + 5000 = 15666 USD
  • SPY is trading at 597.58 USD => (107923.82 - 15666) / 597.58 = 154.4 shares to own.
  • Owned SPY shares : 158
  • Instead of selling shares I bring equivalent of 4 shares in cash : 4 x 597.58 => 2400 USD
Classified in : Options - Tags : none

Raspberry Pi + SSD merger

Written by pmd - - no comments

My Raspberry Model 3B V1.2, cooled by an armor case, and SSD on which it boots laid on the floor and dust for many years.
I wanted to improve this. This would look better and area would be easier to clean weekly.

I started looking for 3D printing parts that would allow Raspeberry and SSD to hold together.

I found this: Raspberry Pi + Armor Case + SSD

This case has been designed for Raspberry Pi 4 but I decided to try it. It worked very well.

I got the 4 parts printed for about 8 EUR.
Here are the parts, in case above website disapears:

This allowed me to do that finale installation:

Looks tidy, and it is easy to clean the floor !

Wireguard server on Raspberry while being an OpenVPN client

Written by pmd - - no comments

This is follwoing these first notes written a while ago : Wireguard on Raspberry

Context

The Raspberry is already an OpenVPN client : see here.

  • All packets that are not tagged '42' are using main route table (which outputs to OpenVPN tunnel).
  • All packets that are tagged '42' are using route table 42 (which outputs to internet link).

For my use case, all Wireguard packets will need to be routed as specified in table 42.

Wireguard server setup

I used this guide to globally setup wireguard and a few clients (lastest updated using iptables): Installing and Configuring WireGuard on Raspberry Pi OS (September 2021)

The generated configuration was the following:

Server:

$ sudo cat /etc/wireguard/wg0.conf
[Interface]
Address = 192.168.99.1/24
ListenPort = 58280
PrivateKey = gNVxJe7Se842IiOR5GsXeM4sHcacGhPATIdQCgqP8Wa=
PostUp = iptables -A FORWARD -i %i -j ACCEPT; iptables -A FORWARD -o %i -j ACCEPT; iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
PostDown = iptables -D FORWARD -i %i -j ACCEPT; iptables -D FORWARD -o %i -j ACCEPT; iptables -t nat -D POSTROUTING -o eth0 -j MASQUERADE
[Peer]
PublicKey = OQmmvh9/8PDWFIpOEzVWzOZ1HXQ48+10vONFlUNb0ia=
AllowedIPs = 192.168.99.2/32

Peer 1:

$ cat ~/wg_config/users/client1/client.conf
[Interface]
Address = 192.168.99.2/24
PrivateKey = 6OfJPX1ZQCFu08fTy2uU6JdgUf/qXgzBoTtX/tCYX3a=

[Peer]
PublicKey = b6kqDH4pjAdK0LqPrEF4Fc9d4XxR0Eb3kSk9rzdEKma=
AllowedIPs = 192.168.99.1/32, 192.168.1.0/24
Endpoint = adress.ddns.net:58280

Adding other users

⚠️ ⚠️ ⚠️ Be sure to make a copy of your wireguard configuration because it will be overwritten.

$ sudo cp /etc/wireguard/wg0.conf /etc/wireguard/wg0.conf.bak  # make a backup if necessary !
$ sudo wg-quick up wg0                                         # make sure Wireguard is running
$ sudo ./wg_config/user.sh -a another_user                     # creating new user
$ ls ./wg_config/users/another_user/                           # look at all files generated
total 32K
drwxr-xr-x 2 root root 4.0K Sep 21 15:56 .
drwxr-xr-x 6 root root 4.0K Oct  9 18:16 ..
-rw-r--r-- 1 root root  216 Sep 21 15:56 client.all.conf
-rw-r--r-- 1 root root  238 Sep 21 15:56 client.conf
-rw-r--r-- 1 root root  900 Sep 21 15:56 another_user.all.png
-rw-r--r-- 1 root root 1016 Sep 21 15:56 another_user.png
-rw-r--r-- 1 root root   45 Sep 21 15:56 privatekey
-rw-r--r-- 1 root root   45 Sep 21 15:56 publickey
$ sudo wg-quick down wg0                                       # switch off wireguard server
$ sudo cat /etc/wireguard/wg0.conf                             # to copy the new user lines configuration
$ sudo nano /etc/wireguard/wg0.conf.bak                        # to paste the new user lines configuration
$ sudo rm /etc/wireguard/wg0.conf                              # delete incomplete configuration
$ sudo mv /etc/wireguard/wg0.conf.bak /etc/wireguard/wg0.conf  # install proper configuration
$ sudo wg-quick up wg0                                         # restart server with updated configuration

IP forwarding

For clients to be able to join each other and access internet, it is necessary to enable IP forwarding:

$ sysctl net.ipv4.ip_forward
net.ipv4.ip_forward = 0
$ sudo sysctl -w 'net.ipv4.ip_forward=1'
net.ipv4.ip_forward = 1
$ sysctl net.ipv4.ip_forward
net.ipv4.ip_forward = 1

Adjustment

Because of the context described above it wasn't enough to have a working link.

I had to add few lines for it to work on server and client sides. See below:

Server:

$ sudo cat /etc/wireguard/wg0.conf
[Interface]
Address = 192.168.99.1/24
ListenPort = 58280
PrivateKey = gNVxJe7Se842IiOR5GsXeM4sHcacGhPATIdQCgqP8Wa=
FwMark = 0x2A # if packet not tagged '42' it will be routed to tun0 interface
PostUp = iptables -A FORWARD -i %i -j ACCEPT; iptables -A FORWARD -o %i -j ACCEPT; iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
PostUp = ip route add 192.168.99.0/24 dev wg0 proto kernel scope link src 192.168.99.1 table 42 # route table update for packet tagged '42'
PostUp = sysctl -w 'net.ipv4.ip_forward=1' # activate IP forwarding
PostUp = ip rule add from 192.168.99.0/24 table 42; ip rule add to 192.168.99.0/24 table 42 # all packet comming/leaving from 192.168.99.0/24 should use table 42

PostDown = iptables -D FORWARD -i %i -j ACCEPT; iptables -D FORWARD -o %i -j ACCEPT; iptables -t nat -D POSTROUTING -o eth0 -j MASQUERADE
PostDown = sysctl -w 'net.ipv4.ip_forward=0' # disactivate IP forwarding
PostDown = ip rule del from 192.168.99.0/24 table 42; ip rule del to 192.168.99.0/24 table 42

[Peer]
PublicKey = OQmmvh9/8PDWFIpOEzVWzOZ1HXQ48+10vONFlUNb0ia=
AllowedIPs = 192.168.99.2/32

Sources: FwMark, PostUp.

Peer 1:

$ cat ~/wg_config/users/client1/client.conf
[Interface]
Address = 192.168.99.2/24
PrivateKey = 6OfJPX1ZQCFu08fTy2uU6JdgUf/qXgzBoTtX/tCYX3a=
DNS = 208.67.222.222, 208.67.220.220

[Peer]
PublicKey = b6kqDH4pjAdK0LqPrEF4Fc9d4XxR0Eb3kSk9rzdEKma=
AllowedIPs = 0.0.0.0/0
Endpoint = adress.ddns.net:58280

Source DNS, DNS.

Start, monitor and stop wireguard

$ sudo wg-quick up wg0
[#] ip link add wg0 type wireguard
[#] wg setconf wg0 /dev/fd/63
[#] ip -4 address add 192.168.99.1/24 dev wg0
[#] ip link set mtu 1420 up dev wg0
[#] iptables -A FORWARD -i wg0 -j ACCEPT; iptables -A FORWARD -o wg0 -j ACCEPT; iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
[#] ip route add 192.168.99.0/24 dev wg0 proto kernel scope link src 192.168.99.1 table 42
[#] sysctl -w 'net.ipv4.ip_forward=1'
net.ipv4.ip_forward = 1
[#] ip rule add from 192.168.99.0/24 table 42; ip rule add to 192.168.99.0/24 table 42
$
$ sudo wg
interface: wg0
  public key: b6kqDH4pjAdK0LqPrEF4Fc9d4XxR0Eb3kSk9rzdEKma=
  private key: (hidden)
  listening port: 58280
  fwmark: 0x2a

peer: OQmmvh9/8PDWFIpOEzVWzOZ1HXQ48+10vONFlUNb0ia=

  endpoint: 96.82.73.111:32378
  allowed ips: 192.168.99.2/32
  latest handshake: 37 seconds ago
  transfer: 425.32 KiB received, 502.92 KiB sent
$
$ sudo wg-quick down wg0
[#] ip link delete dev wg0
[#] iptables -D FORWARD -i wg0 -j ACCEPT; iptables -D FORWARD -o wg0 -j ACCEPT; iptables -t nat -D POSTROUTING -o eth0 -j MASQUERADE
[#] sysctl -w 'net.ipv4.ip_forward=0'
net.ipv4.ip_forward = 0
[#] ip rule del from 192.168.99.0/24 table 42; ip rule del to 192.168.99.0/24 table 42
$

Enabling at startup

Once everything is working you can enable Wireguard at startup by doing:

$ sudo systemctl enable wg-quick@wg0

Avoid unwanted, phontom, ghost mobile data billing

Written by pmd - - no comments

On a French SIM card supplied by Syma Mobile (wiki), I was being charged with incomprehensible data:

  • roaming was disabled !
  • and later I even disabled mobile data !

Still I was billed data cost.

 

And I was not alone. This is why: Phantom Roaming

Learn more reading other various links:

 

What I did to reliably avoid such cost while avoiding Plane Mode (so I keep the possibility to receive/answer calls and sms abroad):

  1. Disable roaming for the SIM you don't want to be charged roaming abroad.
  2. Force the SIM to use a no data protocol to establish connection with antennas: 2G (GSM) or 3G (WCDMA).

April 2025: It's now more than a year that I avoid being charged ghost roaming thanks to this technic.

OpenWRT on GL-AR750 + E3372 in NCM + receive SMS (2023)

Written by pmd - - no comments

⚫ Installed last available OpenWRT on AR750

login as: root
root@192.168.3.1's password:


BusyBox v1.35.0 (2023-04-27 20:28:15 UTC) built-in shell (ash)

  _______                     ________        __
 |       |.-----.-----.-----.|  |  |  |.----.|  |_
 |   -   ||  _  |  -__|     ||  |  |  ||   _||   _|
 |_______||   __|_____|__|__||________||__|  |____|
          |__| W I R E L E S S   F R E E D O M
 -----------------------------------------------------
 OpenWrt 22.03.5, r20134-5f15225c1e
 -----------------------------------------------------
root@OpenWrt:~#

Source: [OpenWrt Wiki] GL.iNet GL-AR750

⚫ Expected configuration

One OpenWRT router getting internet access through a 4G USB dongle using NCM connection (much faster than PPP).

⚫ Configuration

⚪ Installing necessary packets on OpenWRT

# opkg update
# opkg installkmod-usb2 kmod-usb3 kmod-usb-net-huawei-cdc-ncm comgt-ncm kmod-usb-serial kmod-usb-serial-option kmod-usb-serial-wwan luci-proto-ncm usb-modeswitch kmod-usb-serial-ipw
# opkg install nano curl socat

Sources:

  1. Installer le firmware OpenWrt sur un routeur Wi-Fi
  2. Huawei E3272 и OpenWRT: klink0v — LiveJournal

⚪ Preparing the 4G USB dongle (Huawei E3272)

I bought for cheap money an old second-hand Huawei E3272. It was loaded with a HiLink software (22.X) and SIM locked.

It took me a day of struggle to sim unlock it and finally install a working stick software (21.X).

Sources:

Finally I installed a HiLink software + WEBUI to validate good working of the 4D USB dongle on Windows 10 then installed a stick software.
All downloaded from 3ginfo.ru :

  • Huawei_E3272_Firmware_22.436.07.00.00_Universal_3Ginfo.ru.7z
  • Huawei_E3272s_WebUI_17.100.08.00.03_general_3Ginfo.ru.7z
  • Huawei_E3272s-153_Firmware_21.436.11.00.00_Universal_3Ginfo.ru.7z

All of this had to be done on a Windows 7 computer. Impossible to do on a Windows 10 computer.

⚫ Testing

⚪ Checking Huawei E3272 on OpenWRT

root@OpenWrt:~# socat - /dev/cdc-wdm0,crnl

AT

OK
AT^SYSINFOEX

^SYSINFOEX:2,3,0,1,,6,"LTE",101,"LTE"

OK
AT^SYSCFGEX=?

^SYSCFGEX: ("00","01","02","03","99"),((2000000400380,"GSM900/GSM1800/WCDMA900/WCDMA2100"),(4280000,"GSM850/GSM1900/WCDMA850"),(3fffffff,"All bands")),(0-2),(0-4),((c5,"LTE_B1/LTE_B3/LTE_B7/LTE_B8"),(7fffffffffffffff,"All bands"))

OK
ATI

Manufacturer: huawei
Model: E3272
Revision: 21.436.11.00.00
IMEI: XXXXXXXXXXXXXXX
+GCAP: +CGSM,+DS,+ES

OK
AT^FHVER

^FHVER:"E3272S-600 21.436.11.00.00,CH1E3272SM Ver.A"

OK
AT^VERSION?

^VERSION:BDT:Aug 30 2013, 12:47:28
^VERSION:EXTS:21.436.11.00.00
^VERSION:INTS:
^VERSION:EXTD:WEBUI_17.100.08.00.03_Hilink_V7R1_V3R2_V3R3
^VERSION:INTD:
^VERSION:EXTH:CH1E3272SM Ver.A
^VERSION:INTH:
^VERSION:EXTU:E3272
^VERSION:INTU:
^VERSION:CFG:1004
^VERSION:PRL:

OK
AT^SETPORT=?

^SETPORT:1: 3G MODEM
^SETPORT:2: 3G PCUI
^SETPORT:3: 3G DIAG
^SETPORT:5: 3G GPS
^SETPORT:A: BLUE TOOTH
^SETPORT:16: NCM
^SETPORT:A1: CDROM
^SETPORT:A2: SD
^SETPORT:10: 4G MODEM
^SETPORT:12: 4G PCUI
^SETPORT:13: 4G DIAG
^SETPORT:14: 4G GPS

OK
AT^SETPORT?

^SETPORT:A1,A2;10,12,16,A1,A2

OK


^Croot@OpenWrt:~#
root@OpenWrt:~#
root@OpenWrt:~#

⚪ Speed test

I made a speed test with PPP and NCM protocol to verify the common speech readable on internet:

PPP (wikipedia) NCM (wikipedia)

NCM is definitly better performing.

⚪ USSD and SMS and Telegram

To be checked and tried:

Telegram

Send a message to telegram chat:

curl -X POST -H "Content-Type:multipart/form-data" -F chat_id=<CHAT_ID> -F text="message to test !!!" "https://api.telegram.org/bot<token_from_@BotFather>/sendMessage"
curl -s -X POST -H "Content-Type:multipart/form-data" -F chat_id=<CHAT_ID> -F text="message to test !!!" "https://api.telegram.org/bot<token_from_@BotFather>/sendMessage" | jq '.ok'

Get updates from telegram chats:

curl -s https://api.telegram.org/bot<token_from_@BotFather>/getUpdates | jq
curl -s https://api.telegram.org/bot<token_from_@BotFather>/getUpdates | jq '.result[].message.text'
curl -s https://api.telegram.org/bot<token_from_@BotFather>/getUpdates | jq '.result[].message.date'

Send a message+file to telegram chat:

curl -s -X POST https://api.telegram.org/bot<token_from_@BotFather>/sendDocument -F chat_id=<CHAT_ID> -F document=@'/path/to/document' -F caption='your message here'

Receive SMS with smstools3 and forward to Telegram

opkg install kmod-usb-serial kmod-usb-serial-wwan kmod-usb-serial-option usb-modeswitch smstools3 curl iconv jq bc
mkdir /usr/local
mkdir /usr/local/bin
nano /usr/local/bin/pushsms

This file will be called as soon as a new SMS is received in order to transfer it to a Telegram chat:

#!/bin/sh
# /usr/local/bin/pushsms
# chmod +x /usr/local/bin/pushsms

chat_id=<CHAT_ID>
token=<token_from_@BotFather>

if [ "$1" == "RECEIVED" ] || [ "$1" == "REPORT" ]; then
   from=`grep "From:" $2 | awk -F ': ' '{printf $2}'`
   #sent=`grep "Sent:" $2 | awk -F ': ' '{printf $2}'`
   #received=`grep "Received:" $2 | awk -F ': ' '{printf $2}'`
   alphabet=`grep "Alphabet:" $2 | awk -F ': ' '{printf $2}'`

   if [ "$alphabet" = "UCS2" ]; then
       content=$(sed -e '1,/^$/ d' < "$2" | iconv -f UNICODEBIG -t UTF-8)
   else
       content=$(sed -e '1,/^$/ d' < "$2" | iconv -f "windows-1252" -t UTF-8)
   fi

   text=$(cat << EOF
$content
From $from
EOF
)

   curl -s -d "chat_id=$chat_id&text=$text&disable_web_page_preview=true" -X POST https://api.telegram.org/bot"$token"/sendMessage

fi

Don't forget to make above file executable.

Now editing the configuration of smstools3:

# /etc/smsd.conf
#
# Description: Main configuration file for the smsd
#

devices = GSM1
incoming = /var/spool/sms/incoming
outgoing = /var/spool/sms/outgoing
checked = /var/spool/sms/checked
failed = /var/spool/sms/failed
sent = /var/spool/sms/sent
receive_before_send = no
autosplit = 3
logfile = 1
loglevel = 5
eventhandler = /usr/local/bin/pushsms

# Uncomment (and edit) this section to allow smsd to start:
#
[GSM1]
init = AT+CPMS="ME","ME","ME"
device = /dev/cdc-wdm0
incoming = yes
#pin = 0000
baudrate = 9600
signal_quality_ber_ignore = yes
detect_unexpected_input = no
memory_start = 0
cs_convert_optical = no
report = yes

Now restart the utility:

/etc/init.d/smstools3 restart

And check logs:

logread | grep smsd

Send SMS with smstools3

sendsms 491721234567 'Hello, how are you'

Some method to send USSD code from command line

root@OpenWrt:~# socat - /dev/cdc-wdm0,crnl
AT+CUSD=1,"AA18CC3602",15

OK

^MODE: 5,4

^RSSI: 26

+CUSD: 0,"041204300448002004370430043F0440043E044100200432002004340435043B04350020003A002900200421043A043E0440043E0020043F04400438043B043504420438044200200053004D00530020043E00200432044B043F043E043B043D0435043D043804380021",72

OK
^Croot@OpenWrt:~#

Open kinda serial communication link with modem:

root@OpenWrt:~# socat - /dev/cdc-wdm0 << EOF
> AT+CUSD=1,"AA18CC3602",15
> EOF


OK

root@OpenWrt:~#
root@OpenWrt:~# cat /dev/cdc-wdm0 | grep +CUSD: > ./USSDresult.txt &
root@OpenWrt:~# echo -e -n "AT+CUSD=1,\"AA18CC3602\",15\r\n" > /dev/cdc-wdm0
root@OpenWrt:~# killall cat
[1]+ Done cat /dev/cdc-wdm0 | grep +CUSD: 1>./USSDresult.txt
root@OpenWrt:~# cat ./USSDresult.txt
+CUSD: 0,"041204300448002004370430043F0440043E044100200432002004340435043B04350020003A002900200421043A043E0440043E0020043F04400438043B043504420438044200200053004D00530020043E00200432044B043F043E043B043D0435043D043804380021",72
root@OpenWrt:~# cat ./USSDresult.txt | sed -e 's@+CUSD: 0,"@@g;s@",15@@g;s@",72@@g'
041204300448002004370430043F0440043E044100200432002004340435043B04350020003A002900200421043A043E0440043E0020043F04400438043B043504420438044200200053004D00530020043E00200432044B043F043E043B043D0435043D043804380021
root@OpenWrt:~# rm ./USSDresult.txt
root@OpenWrt:~#

To code the USSD code and decode the answer you can use this tool: SMSTools3 PDU Converter. Or these two first shell script functions below. Third function to code USSD, catch the answer and decode it:

#!/bin/sh
# Idea: https://github.com/Shumaher/huawei-ussd/blob/master/ussd.sh
# But slightly modified to make it work

decodeUCS2() # UCS2 to text (to decode USSD answer)
{
   bytes=$(echo -n $1 | sed "s/\(.\{2\}\)/\\\x\1/g")
   REPLY=$(printf $bytes | iconv -f UNICODEBIG -t UTF-8)
   echo -n "$REPLY"
}

encodePDU() # text to PDU (to code the USSD)
{
   in=$1
   let "in_len=${#in}-1"
   for chr in $(seq  0 $in_len)
   do
       let "chr2=$chr+1"
       let "t=$chr%8+1"
       if [ "$t" -ne 8 ]; then
           byte=$(printf "%d" "'${in:$chr:1}")
           let "c=$byte>>($chr%8)"
           let "c2=(1<<$t)-1"
           byte2=$(printf "%d" "'${in:$chr2:1}")
           let "b=$byte2 & $c2"
           let "c=$b<<(8-$t) | $c"
           REPLY=$REPLY$(echo "obase=16; $c" | bc | sed 's/\<[0-9A-F]\>/0&/' )
       fi
   done
       # echo "AT-command to send '$1' as USSD-request: 'AT+CUSD=1,\"$REPLY\",15"
       echo -n "$REPLY"
}

sendUSSD() # To send an USSD code to operator and get result
{
   ussd_human=$1
   ussd_pdu=$(encodePDU $ussd_human)

   rm -f ./USSDresult.txt
   cat /dev/cdc-wdm0 | grep "+CUSD: 0," > ./USSDresult.txt &
   echo -e -n "AT+CUSD=1,\"$ussd_pdu\",15\r\n" > /dev/cdc-wdm0
   # wait up to 10 seconds for the answer
   sleeptime=10
   while [ $(cat ./USSDresult.txt | grep -c "+CUSD: 0,") -le 0 ] && [ $sleeptime -ge 1 ]
   do
       sleep 1
       sleeptime=$(($sleeptime - 1))
   done
   killall cat
   # here could add an 'if' statement in case 10sec waited and answer still not catched
   REPLY=$(cat ./USSDresult.txt | sed -e 's@+CUSD: 0,"@@g;s@",15@@g;s@",72@@g' | tr -d '\r\n')
   REPLY=$(decodeUCS2 "$REPLY")
   echo -n "$REPLY"
   rm ./USSDresult.txt
}

=> sendUSSDcatchANSWER.sh

Various sources:

Rss feed of the articles