Coding

From Calvin & Hobbes to Photo Tagging: Excel’s Unexpected Image Capability

In Excel, using Visual Basic, you can change an image as you scroll. This makes it easy to look at each image and annotate it.

This is how I transcribed every Calvin & Hobbes.

I used this technique first when typing out the strips during my train rides from Bandra to Churchgate. I had an opportunity to re-apply it recently when we needed to tag hundreds of photographs based on a set of criteria.

Here’s how you can do this. Note: This works only on Windows.

STEP 1: Create a new Excel workbook and save it as an Excel macro-enabled workbook. (Note: When opening it again, you need to enable macros)

STEP 2: Open File > Options (Alt-F-T), go to Customize Ribbon. Under “Customize the Ribbon”, enable the “Developer” menu.

STEP 3: In Developer > Insert > ActiveX Controls, select Image and draw a rectangle from A1 to J10. (Resize it later.)

STEP 4: By default, this will be called Image1. In any case, note down the name from the Name box on the top left.

STEP 5: In cells A11 onwards, add paths to file names.

STEP 6: Click Developer > Visual Basic (Alt-F11), go to ThisWorkbook, and paste this code:

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim img As String
    img = Sh.Cells(Target.Row, 1).Value
    If (img <> "" And img <> "file") Then ActiveSheet.Image1.Picture = LoadPicture(img)
End Sub

Replace ActiveSheet.Image1 with ActiveSheet.(whatever) based on your image name in Step 4.

STEP 7: Select Developer > Design Mode. Click on Image1. Then select Developer > Properties. In this panel, under PictureSizeMode, choose 3 - fmPictureSizeModeZoom to fit the picture.

Now scroll through the rows. The images will change.

Embeddings similarity threshold

text-embedding-ada-002 used to give high cosine similarity between texts. I used to consider 85% a reasonable threshold for similarity. I almost never got a similarity less than 50%.

text-embedding-3-small and text-embedding-3-large give much lower cosine similarities between texts.

For example, take these 5 words: “apple”, “orange”, “Facebook”, “Jamaica”, “Australia”. Here is the similarity between every pair of words across the 3 models:

For our words, new text-embedding-3-* models have an average similarity of ~43% while the older text-embedding-ada-002 model had ~85%.

Today, I would use 45% as a reasonable threshold for similarity with the newer models. For example, “apple” and “orange” have a similarity of 45-47% while Jamaica and apple have a ~20% similarity.

Here’s a notebook with these calculations. Hope that gives you a feel to calibrate similarity thresholds.

LLMs can teach experts

I am a fairly good programmer. So, when I see a problem, my natural tendency is to code.

I’m trying to break that pattern. Instead, I ask ChatGPT.

For example, I asked:

Write a compact 1-line Python expression that checks if user.id ends with @gramener.com or @straive.com

user.id.endswith(('@gramener.com', '@straive.com'))

After 15 years of using Python, I learnt that .endswith() supports tuple suffixes. This has been around since Python 2.5 (released in 2006 — before I knew Python.) The documentation has a tiny sentence in the middle saying “suffix can also be a tuple of suffixes to look for.”

I checked with a few colleagues, including Jaidev. They didn’t know it either.

It’s small little things like this that made me conclude.

I’m not going to code anymore. ChatGPT will, instead.

Always use value= for dynamic HTML options

Even after 30 years of HTML, I learn new things about it.

This Monday morning, I woke up to a mail from Sundeep saying requests for a Data Engineer - AWS/Azure/GCP in our internal fulfilment portal raised an error.

My guess was one of these:

  1. The “/” in the role is causing a problem. (Developer mistake.)
  2. The role exists in one table but not the other. (Recruitment team mistake.)
  3. The application wasn’t set up / restarted properly. (IT mistake.)

All three were wrong. So I dug deeper.

The role was defined as Data Engineer  - AWS/Azure/GCP (note the 2 spaces before the hyphen). But the form kept sending Data Engineer - AWS/Azure/GCP (spaces were condensed).

I swear there was NOTHING in the code that changes the options. The relevant line just picked up the role and rendered it inside the <select>:

“`html

<option>{{ row['Role'] }}</option>

I used the browser’s developer tools to inspect the `<select>` element. It showed the options with the 2 spaces:

<option>Data Engineer  - AWS/Azure/GCP</option>

But, when I selected it and printed the value, it had only one space.

> console.log(document.querySelector("#role").value
'Data Engineer - AWS/Azure/GCP'

That’s when it hit me. HTML condenses whitespaces.

Till date, I only ever used <option value=""> when specifying a value different from what’s displayed. I never thought of using it to preserve the value.

LESSON: If you’re dynamically generating <option>s, ALWAYS use value= with the same value as the text.

Cyborg scraping

LinkedIn has a page that shows the people who most recently followed you.

At first, it shows just 20 people. But as you scroll, it keeps fetching the rest. I’d love to get the full list on a spreadsheet. I’m curious about:

  1. What kind of people follow me?
  2. Which of them has the most followers?
  3. Who are my earliest followers?

But first, I need to scrape this list. Normally, I’d spend a day writing a program. But I tried a different approach yesterday.

Aside: it’s easy to get bored in online meetings. I have a surplus of partially distracted time. So rather than writing code to save me time, I’d rather create simple tasks to keep me occupied. Like scrolling.

So here’s my workflow to scrape the list of followers.

Step 1: Keep scrolling all the way to the bottom until you get all followers.

Step 2: Press F12, open the Developer Tools – Console, and paste this code.

copy($$('.follows-recommendation-card').map(v => {
  let name = v.querySelector('.follows-recommendation-card__name')
  let headline = v.querySelector('.follows-recommendation-card__headline')
  let subtext = v.querySelector('.follows-recommendation-card__subtext')
  let link = v.querySelector('.follows-recommendation-card__avatar-link')
  let followers = '', match
  if (subtext) {
    if (match = subtext.innerText.match(/([\d\.K]+) follower/)) {
      followers = match[1]
    } else if (match = subtext.innerText.match(/([\d\.K]+) other/)) {
      followers = match[1]
    }
  }
  followers = followers.match(/K$/) ? parseFloat(followers) * 1000 : parseFloat(followers)
  return {
    name: name ? name.innerText : '',
    headline: headline ? headline.innerText : '',
    followers: followers,
    link: link ? link.href : ''
  }
}))

Step 3: The name, headline, followers and link are now in the clipboard as JSON. Visit https://www.convertcsv.com/json-to-csv.htm and paste it in “Select your input” under “Enter Data”.

Step 4: Click on the “Download Result” button. The JSON is converted into a CSV you can load into a spreadsheet.

I call this “Cyborg scraping“. I do half the work (scrolling, copy-pasting, etc.) The code does half the work. It’s manual. It’s a bit slow. But it gets the job done quick and dirty.

I’ll share later what I learned about my followers. For now, I’m looking forward to meetings 😉

PS: A similar script to scrape LinkedIn invitations is below. You can only see 100 invitations per page, though.

copy($$('.invitation-card').map(v => ({
  name: (v.querySelector('.invitation-card__title') || {}).innerText || '',
  link: v.querySelector('.invitation-card__link').href,
  subtitle: (v.querySelector('.invitation-card__subtitle') || {}).innerText || '',
  common: (v.querySelector('.member-insights__count') || {}).innerText || '',
  message: (v.querySelector('.invitation-card__custom-message') || {}).innerText || '',
})))

PS: A similar script to scrape LinkedIn people search results is below.

copy($$('.entity-result').map(v => {
  const name = v.querySelector('.entity-result__title-text [aria-hidden="true"]');
  const link = v.querySelector('a');
  const badge = v.querySelector('.entity-result__badge [aria-hidden="true"]');
  const title = v.querySelector('.entity-result__primary-subtitle');
  const subtitle = v.querySelector('.entity-result__secondary-subtitle');
  const summary = v.querySelector('.entity-result__summary--2-lines');
  const insight = v.querySelector(".entity-result__simple-insight-text");
  return {
    name: name?.innerText || '',
    link: (link?.href || '').split('?')[0],
    badge: badge?.innerText || '',
    title: title?.innerText || '',
    subtitle: subtitle?.innerText || '',
    summary: summary?.innerText || '',
    insight: insight?.innerText || '',
  }
}))

Releasing modified mosquitoes precisely

At PyCon Indonesia, I spoke about a project we worked on with the World Mosquito Program.

The World Mosquito Program (WMP) modifies mosquitoes with a bacteria — Wolbachia. This reduces their ability to carry deadly viruses. (It makes me perversely happy that we’re infecting mosquitoes now 😉.)

Modifying mosquitoes is an expensive process. With a limited set of “good mosquitoes”, it is critical to find the best release points that will help them replicate rapidly.

But planning the release points took weeks of manual effort. It involved ground personnel going through several iterations.

So our team took high-resolution satellite images, figured out the building density, estimated population density based on that, and generated a release plan. This model is 70% more accurate and reduced the time from 3 weeks to 2 hours.

More details at the Gramener website.

The slides for the talk are below.

Programming Minecraft with Websockets

Minecraft lets you connect to a websocket server when you’re in a game. The server can receive and send any commands. This lets you build a bot that you can … (well, I don’t know what it can do, let’s explore.)

Minecraft has commands you can type on a chat window. For example, type / to start a command and type setblock ~1 ~0 ~0 grass changes the block 1 north of you into grass. (~ means relative to you. Coordinates are specified as X, Y and Z.)

Minecraft grass block

Note: These instructions were tested on Minecraft Bedrock 1.16. I haven’t tested them on the Java Edition.

Connect to Minecraft

You can send any command to Minecraft from a websocket server. Let’s use JavaScript for this.

First, run npm install ws uuid. (We need ws for websockets and uuid to generate unique IDs.)

Then create this mineserver1.js:

const WebSocket = require('ws')
const uuid = require('uuid')        // For later use

// Create a new websocket server on port 3000
console.log('Ready. On MineCraft chat, type /connect localhost:3000')
const wss = new WebSocket.Server({ port: 3000 })

// On Minecraft, when you type "/connect localhost:3000" it creates a connection
wss.on('connection', socket => {
  console.log('Connected')
})

On Minecraft > Settings > General > Profile, turn off the “Require Encrypted Websockets” setting.

Run node mineserver1.js. Then type /connect localhost:3000 in a Minecraft chat window. You’ll see 2 things:

  1. MineCraft says “Connection established to server: ws://localhost:3000”
  2. Node prints “Connected”

Now, our program is connected to Minecraft, and can send/receive messages.

Minecraft chat connect

Notes:

  • The Python equivalent is in mineserver1.py. Run python mineserver1.py.
  • If you get an Uncaught Error: Cannot find module 'ws', make sure you ran npm install ws uuid.
  • If you get an “Encrypted Session Required” error, make sure you turned off the “Require Encrypted Websockets” setting mentioned above.
  • To disconnect, run /connect off

Subscribe to chat messages

Now let’s listen to the players’ chat.

A connected websocket server can send a “subscribe” message to Minecraft saying it wants to “listen” to specific actions. For example, you can subscribe to “PlayerMessage”. Whenever a player sents a chat message, Minecraft will notify the websocket client.

Here’s how to do that. Add this code in the wss.on('connection', socket => { ... }) function.

  // Tell Minecraft to send all chat messages. Required once after Minecraft starts
  socket.send(JSON.stringify({
    "header": {
      "version": 1,                     // We're using the version 1 message protocol
      "requestId": uuid.v4(),           // A unique ID for the request
      "messageType": "commandRequest",  // This is a request ...
      "messagePurpose": "subscribe"     // ... to subscribe to ...
    },
    "body": {
      "eventName": "PlayerMessage"      // ... all player messages.
    },
  }))

Now, every time a player types something in the chat window, the socket will receive it. Add this code below the above code:

  // When MineCraft sends a message (e.g. on player chat), print it.
  socket.on('message', packet => {
    const msg = JSON.parse(packet)
    console.log(msg)
  })

This code parses all the messages it receives and prints them.

This code in is mineserver2.js. Run node mineserver2.js. Then type /connect localhost:3000 in a Minecraft chat window. Then type a message (e.g. “alpha”) in the chat window. You’ll see a message like this in the console.

{
  header: {
    messagePurpose: 'event',        // This is an event
    requestId: '00000000-0000-0000-0000-000000000000',
    version: 1                      // using version 1 message protocol
  },
  body: {
    eventName: 'PlayerMessage',
    measurements: null,
    properties: {
      AccountType: 1,
      ActiveSessionID: 'e0afde71-9a15-401b-ba38-82c64a94048d',
      AppSessionID: 'b2f5dddc-2a2d-4ec1-bf7b-578038967f9a',
      Biome: 1,                     // Plains Biome. https://minecraft.gamepedia.com/Biome
      Build: '1.16.201',            // That's my build
      BuildNum: '5131175',
      BuildPlat: 7,
      Cheevos: false,
      ClientId: 'fcaa9859-0921-348e-bc7c-1c91b72ccec1',
      CurrentNumDevices: 1,
      DeviceSessionId: 'b2f5dddc-2a2d-4ec1-bf7b-578038967f9a',
      Difficulty: 'NORMAL',         // I'm playing on normal difficulty
      Dim: 0,
      GlobalMultiplayerCorrelationId: '91967b8c-01c6-4708-8a31-f111ddaa8174',
      Message: 'alpha',             // This is the message I typed
      MessageType: 'chat',          // It's of type chat
      Mode: 1,
      NetworkType: 0,
      Plat: 'Win 10.0.19041.1',
      PlayerGameMode: 1,            // Creative. https://minecraft.gamepedia.com/Commands/gamemode
      Sender: 'Anand',              // That's me.
      Seq: 497,
      WorldFeature: 0,
      WorldSessionId: '8c9b4d3b-7118-4324-ba32-c357c709d682',
      editionType: 'win10',
      isTrial: 0,
      locale: 'en_IN',
      vrMode: false
    }
  }
}

Notes:

Build structures using chat

Let’s create a pyramid of size 10 around us when we type pyramid 10 in the chat window.

The first step is to check if the player sent a chat message like pyramid 10 (or another number). Add this code below the above code:

  // When MineCraft sends a message (e.g. on player chat), act on it.
  socket.on('message', packet => {
    const msg = JSON.parse(packet)
    // If this is a chat window
    if (msg.body.eventName === 'PlayerMessage') {
      // ... and it's like "pyramid 10" (or some number), draw a pyramid
      const match = msg.body.properties.Message.match(/^pyramid (\d+)/i)
      if (match)
        draw_pyramid(+match[1])
    }
  })

If the user types “pyramid 3” on the chat window, draw_pyramid(3) is called.

In draw_pyramid(), let’s send commands to build a pyramid. To send a command, we need to create a JSON with the command (e.g. setblock ~1 ~0 ~0 grass). Add this code below the above code:

  function send(cmd) {
    const msg = {
      "header": {
        "version": 1,
        "requestId": uuid.v4(),     // Send unique ID each time
        "messagePurpose": "commandRequest",
        "messageType": "commandRequest"
      },
      "body": {
        "version": 1,               // TODO: Needed?
        "commandLine": cmd,         // Define the command
        "origin": {
          "type": "player"          // Message comes from player
        }
      }
    }
    socket.send(JSON.stringify(msg))  // Send the JSON string
  }

Let’s write draw_pyramid() to create a pyramid using glowstone by adding this code below the above code:

  // Draw a pyramid of size "size" around the player.
  function draw_pyramid(size) {
    // y is the height of the pyramid. Start with y=0, and keep building up
    for (let y = 0; y < size + 1; y++) {
      // At the specified y, place blocks in a rectangle of size "side"
      let side = size - y;
      for (let x = -side; x < side + 1; x++) {
        send(`setblock ~${x} ~${y} ~${-side} glowstone`)
        send(`setblock ~${x} ~${y} ~${+side} glowstone`)
        send(`setblock ~${-side} ~${y} ~${x} glowstone`)
        send(`setblock ~${+side} ~${y} ~${x} glowstone`)
      }
    }
  }

This code in is mineserver3.js.

  • Run node mineserver3.js.
  • Then type /connect localhost:3000 in a Minecraft chat window.
  • Then type pyramid 3 in the chat window.
  • You’ll be surrounded by a glowstone pyramid.
Minecraft glowstone pyramid

Notes:

  • The Python equivalent is in mineserver3.py. Run python mineserver3.py.
  • The “requestId” needs to be a UUID — at least for block commands. I tried unique “requestId” values like 1, 2, 3 etc. That didn’t work.

Understand Minecraft’s responses

For every command you send, Minecraft sends a response. It’s “header” looks like this:

{
  "header": {
    "version": 1,
    "messagePurpose": "commandResponse",                  // Response to your command
    "requestId": "97dee9a3-a716-4caa-aef9-ddbd642f2650"   // ... and your requestId
  }
}

If the command is successful, the response has body.statusCode == 0. For example:

{
  "body": {
    "statusCode": 0,                  // No error
    "statusMessage": "Block placed",  // It placed the block you wanted
    "position": { "x": 0, "y": 64, "z": 0 }   // ... at this location
  },
}

If the command failed, the response has a negative body.statusCode. For example:

{
  "body": {
    "statusCode": -2147352576,        // This is an error
    "statusMessage": "The block couldn't be placed"
  },
}

To print these, add this to socket.on('message', ...):

    // If we get a command response, print it
    if (msg.header.messagePurpose == 'commandResponse')
      console.log(msg)

This code in is mineserver4.js.

  • Run node mineserver4.js.
  • Then type /connect localhost:3000 in a Minecraft chat window.
  • Then type pyramid 3 in the chat window.
  • You’ll be surrounded by a glowstone pyramid, and the console will show every command response.

Notes on common error messages:

  • The block couldn't be placed (-2147352576): The same block was already at that location.
  • Syntax error: Unexpected "xxx": at "~0 ~9 ~-1 >>xxx<<" (-2147483648): You gave wrong arguments to the command.
  • Too many commands have been requested, wait for one to be done (-2147418109): Minecraft only allows 100 commands can be executed without waiting for their response.
  • More error messages here.

Wait for commands to be done

Typing “pyramid 3” works just fine. But try “pyramid 5” and your pyramid is incomplete.

Minecraft incomplete pyramid

That’s because Minecraft only allows up to 100 messages in its queue. On the 101st message, you get a Too many commands have been requested, wait for one to be done error.

{
  "header": {
    "version": 1,
    "messagePurpose": "error",
    "requestId": "a5051664-e9f4-4f9f-96b8-a56b5783117b"
  },
  "body": {
    "statusCode": -2147418109,
    "statusMessage": "Too many commands have been requested, wait for one to be done"
  }
}

So let’s modify send() to add to a queue and send in batches. We’ll create two queues:

  const sendQueue = []        // Queue of commands to be sent
  const awaitedQueue = {}     // Queue of responses awaited from Minecraft

In wss.on('connection', ...), when Minecraft completes a command, we’ll remove it from the awaitedQueue. If the command has an error, we’ll report it.

    // If we get a command response
    if (msg.header.messagePurpose == 'commandResponse') {
      // ... and it's for an awaited command
      if (msg.header.requestId in awaitedQueue) {
        // Print errors 5(if any)
        if (msg.body.statusCode < 0)
          console.log(awaitedQueue[msg.header.requestId].body.commandLine, msg.body.statusMessage)
        // ... and delete it from the awaited queue
        delete awaitedQueue[msg.header.requestId]
      }
    }
    // Now, we've cleared all completed commands from the awaitedQueue.

Once we’ve processed Minecraft’s response, we’ll send pending messages from sendQueue, upto 100 and add them to the awaitedQueue.

     // We can send new commands from the sendQueue -- up to a maximum of 100.
     let count = Math.min(100 - Object.keys(awaitedQueue).length, sendQueue.length)
     for (let i = 0; i < count; i++) {
       // Each time, send the first command in sendQueue, and add it to the awaitedQueue
       let command = sendQueue.shift()
       socket.send(JSON.stringify(command))
       awaitedQueue[command.header.requestId] = command
     }
     // Now we've sent as many commands as we can. Wait till the next PlayerMessage/commandResponse

Finally, in function send(), instead of socket.send(JSON.stringify(msg)), we use sendQueue.push(msg) to add the message to the queue.

This code in is mineserver5.js.

  • Run node mineserver5.js.
  • Then type /connect localhost:3000 in a Minecraft chat window.
  • Then type pyramid 6 in the chat window.
  • You’ll be surrounded by a large glowstone pyramid.
  • The console will print messages like setblock ~0 ~6 ~0 glowstone The block couldn't be placed because we’re trying to place duplicate blocks.
Minecraft glowstone pyramid

How to extend Markdown with custom blocks

One problem I’ve had in Markdown is rendering a content in columns.

On Bootstrap, the markup would look like this:

<div class="row">
  <div class="col">...</div>
  <div class="col">...</div>
</div>

How do we get that into Markdown without writing HTML?

On Python, the attribute lists extension lets you add a class. For example:

This is some content
 {: .row}

… renders <p class="row">This is some content</p>.

But I can’t do that to multiple paragraphs. Nor can I next content, i.e. add a .col inside the .row.

Enter markdown-customblocks. It’s a Python module that extends Python Markdown. This lets me write:

::: row
     ::: col
       Content in column 1
     ::: col
       Content in column 2
 ::: row
     ::: col
       Content in column 1
     ::: col
       Content in column 2

This translates to:

<div class="row">
  <div class="col">Content in column 1</div>
  <div class="col">Content in column 2</div>
</div>

Better yet, we can create our own custom HTML block types. For example, this code:

from markdown import Markdown
from customblocks import CustomBlocksExtension

def audio(ctx, src, type="audio/mp3"):
    return f'''<audio src="{src}" type="{type}">
      {ctx.content}
    </audio>'''

md = Markdown(extensions=[
    CustomBlocksExtension(generators={
        'audio': audio
    }),
])

… lets you convert this piece of Markdown:

md.convert('''
::: audio src="mymusic.ogg" type="audio/ogg"
    Your browser does not support `audio`.
''')

… into this HTML:

<audio src="mymusic.ogg" type="audio/ogg">
  Your browser does not support <code>audio</code>.
</audio>

markdown-customblocks is easily the most useful Python module I discovered last quarter.

Create SVG with PowerPoint

With Office 365, PowerPoint supports SVG editing. This is really powerful. It means you can draw in PowerPoint and render it on the web — including as interactive or animated visuals.

For example, the SVG in this simulator was created just with PowerPoint.

The process is simple. Draw anything. Select any shapes and right-click. Select Save As Picture… and choose SVG.

For example, you can use PowerPoint to create Smart Art, export it as SVG, and embed it into a page. See this example on CodePen.

The SVG is fairly well structured and easy to edit. The code generated for these 2 simple shapes:

… is quite straight-forward — just two SVG shapes.

<rect x="125.5" y="185.5" width="107" height="107" stroke="#2F528F" stroke-width="1.33333" stroke-miterlimit="8" fill="#4472C4"/>
<path d="M243.5 292.5 297 185.5 350.5 292.5Z" stroke="#2F528F" stroke-width="1.33333" stroke-miterlimit="8" fill="#4472C4" fill-rule="evenodd"/>

I was worried about the lack of SVG authoring tools in Windows. (InkScape is not usable, and Adobe’s tools are complex and expensive.) PowerPoint fits perfectly.

lxml is fast enough

Given the blazing speed of Node.js these days, I expected HTML parsing to be faster on Node than on Python.

So I compared lxml with htmlparser2 — the fastest libraries on Python and JS in parsing the reddit home page (~700KB).

  • lxml took ~8.6 milliseconds
  • htmlparser2 took ~14.5 milliseconds

Looks like lxml is much faster. I’m likely to stick around with Python for pure HTML parsing (without JavaScript) for a while longer.

In [1]: from lxml.html import parse

In [2]: %timeit tree = parse('reddit.html')
8.69 ms ± 190 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
const { Parser } = require("htmlparser2");
const { DomHandler } = require("domhandler");
const fs = require('fs');
const html = fs.readFileSync('reddit.html');
const handler = new DomHandler(function (error, dom) { });

const start = +new Date();
for (var i = 0; i < 100; i++) {
  const parser = new Parser();
  parser.write(html);
  parser.end();
}
const end = +new Date();
console.log((end - start) / 100);

Note: If I run the htmlparser2 code 100 times instead of 10, it only takes 7ms per loop. The more the number of loops, the faster it parses. I guess Node.js optimizes repeated loops. But I’m only interested in the first iteration, since I’ll be parsing files only once.