Hugo inline spreadsheet

Calculating formulas and building dynamic tables directly in a Markdown page.

With Hugo inline shortcodes, you calculate values and build complex, dynamic tables without ever leaving the Markdown page. I discovered this technique when I wrote my post to test the accuracy of a sample of ChatGPT–generated values. Rather than copy my calculations into the page, I realized I could use variables and Hugo built-in Math functions to keep the writing and computation in a single place.

I’ve written before about inline shortcodes, but this use case might be the most interesting one I’ve found. As an authoring experience, I find it intuitive to calculate values directly beside the accompanying prose. And if these calculations or data presentations are unique to one page, separation of concerns is no concern of mine.

This is something akin to literate programming. Surely this technique won’t replace Jupyter notebooks, but it’s an interesting use case for Hugo pages that involve numerical research or demonstration.

Here are some examples.

Examples of Inline calculation

All the numbers in the following sections were calculated from an inline shortcode in the page. Open the accordion after the table to read the code.

Calculate summary statistics

Consider the following array of numbers: [100.12 140.2 134 150 102].

I can use an inline shortcode to calculate summary statistics and populate a Markdown table.

{{ $cost := slice 100.12 140.2 134.0 150.0 102.0 }}

{{ $cost := slice 100.12 140.2 134.0 150.0 102.0 123.21 }}
{{ $sum := math.Sum $cost  }}
{{ $mean := div $sum (len $cost)  }}
{{ $sumdiff := 0.0  }}
{{ range $cost  }}
  {{ $sumdiff = add $sumdiff (pow (sub . $mean) 2)  }}
{{ end  }}
{{ $variance := div $sumdiff (len $cost)  }}
{{ $std := math.Sqrt $variance  }}


| statistic | value                              |
|-----------|------------------------------------|
| sum       | {{ printf "%.2f" $sum  }}      |
| mean      | {{ printf "%.2f" $mean  }}     |
| variance  | {{ printf "%.2f" $variance  }} |
| std       | {{ printf "%.2f" $std }}       |

Use slices to compute a table of dynamic values

This example compares two slices to create a “spreadsheet”. Some conditional formatting changes the CSS of the derived cells whenever the value is less than 0. The table footer gives a summary.

In this case, I use an HTML table rather than a Markdown one. This is to avoid figuring out how to trim whitespace correctly to keep a single continuous Markdown table across a range of generated values.

{{ $cost := slice 100.12 140.2 134.0 150.0 102.0 123.21 }}
{{ $price := slice 135.0 135.0 135.0 200.0 129.99 115.15 }}
{{ $differences := slice }}

<table>
  <thead>
    <tr>
      <th>Cost</th>
      <th>Sale Price</th>
      <th>Difference</th>
    </tr>
  </thead>
  <tbody>

{{ range $index, $costItem := $cost }}
  {{ $priceItem := index $price $index }}
  {{ $difference := sub $priceItem $costItem }}
  {{ $differences = $differences | append $difference }}

      <tr>
        <td>{{ printf "%.2f" $costItem }}</td>
        <td>{{ printf "%.2f" $priceItem }}</td>
        <td>{{ if lt $difference 0}}
        <span style="color:red"> {{ printf "%.2f" $difference }}</span>
        {{else}}
        {{ printf "%.2f" $difference }}
        </td>
        {{end}}

      </tr>
    {{ end }}
  </tbody>
  <tfoot>
    <tr>
      <td colspan="2" style="text-align: right; font-weight: bold;">Total Profit</td>
      <td style="font-weight: bold;">{{ printf "%.2f" (math.Sum $differences) }}</td>
    </tr>
  </tfoot>
</table>

The HTML table could probably be more semantic, but this seems like a good proof of concept.

Embed calculated values in LaTeX formulas

If you use Latex in Hugo, you can substitute the variables in your formulas with variables from an inline shortcode.

The formula for margin of error is as follows: $$ z_{\frac{\alpha}{2}}\cdot\sqrt{\dfrac{ {\hat p}(1-{\hat p})}{n}} $$

In this case, our variables have the following values:

So our margin of error is calculated as follows:

$$ 1.96 \cdot \sqrt{\dfrac{ 0.78(1-0.78)}{ 91 }} = 0.0851 $$

In this case, variables in the inline shortcode store or calculate all values. So if I change the value of a variable, the values in the table, formula, and derived margin of error all change, too.

The formula for margin of error is as follows:
$$  z_{\frac{\alpha}{2}}\cdot\sqrt{\dfrac{ {\hat p}(1-{\hat p})}{n}} $$



{{ $total := 91.0 }}
{{ $errors := 20.0 }}
{{ $zcrit := 1.96 }}
{{ $correct := (sub $total $errors) }}
{{ $error_rate := (div $errors $total) }}
{{ $correct_rate := (div $correct $total ) }}
{{ $std_err :=
  (math.Sqrt
    (div (math.Product $error_rate $correct_rate)
      ($total))
   )
}}
{{ $margin_err := math.Product $zcrit $std_err }}

In this case, our variables have the following values:

| Statistic      | Value        |
|----------------|--------------|
| Sample size, $$ n $$    | {{ $total }}   |
| Sample proportion, $$ {\hat p} $$ | {{printf "%.2f" $correct_rate }}             |
| [Z critical value](https://www.criticalvaluecalculator.com/blog/understanding-zscore-and-zcritical-value-in-statistics-a-comprehensive-guide), $$z_{\frac{\alpha}{2}}$$  | {{ printf "%.2f" $zcrit }}      |

So our calculation is

$$ {{ $zcrit }} \cdot \sqrt{\dfrac{ {{printf "%.2f" $correct_rate}}(1-{{printf "%.2f" $correct_rate}})}{ {{$total}} }} $$

Which gives us a **margin of error of {{printf "%.4f" $margin_err}}**.

Limitations and ideas for development

This is a fun and flexible way to centralize concerns for work that involves numbers, prose, and Hugo.

The main limitation I found was that I had to be careful to make sure all source values were floats, not integers. Otherwise, I got unexpected values. This also means that I used a lot of printf statements to round values to a few decimals. A more robust method to handle these issues would be to pipe the numbers into the cast.ToFloat function and write a shortcode to round to decimals.

If you are doing high-precision calculation, this is probably not viable. Please do not plan works of civil engineering using Hugo inline shortcodes.

For future work, one might experiment with:

• Using remote data to work with source data in a CSV or JSON file
• Using the Store pad to persist values across different shortcodes in the page—or, alternatively, putting the entire page inside an inline shortcode.

There’s also much room to extend the presentation of the data, perhaps with custom inline JS or CSS.