Functional Self-Tracking is The Only Self-Tracking I Do

“I could never do that,” you say.

And I’ve heard it before.

Eating gluten free for the rest of your life, because you were diagnosed with celiac disease? Heard that response (I could never do that) for going on 14 years.

Inject yourself with insulin or fingerstick test your blood glucose 14 times a day? Wear an insulin pump on your body 24/7/365? Wear a CGM on your body 24/7/365?

Yeah, I’ve heard you can’t do that, either. (For 20 years and counting.) Which means I and the other people living with the situations that necessitate these behaviors are…doing this for fun?

We’re not.

More recently, I’ve heard this type of comment come up about tracking what I’m eating, and in particular, tracking what I’m eating when I’m running. I definitely don’t do that for fun.

I have a 20+ year strong history of hating tracking things, actually. When I was diagnosed with type 1 diabetes, I was given a physical log book and asked to write down my blood glucose numbers.

“Why?” I asked. They’re stored in the meter.

The answer was because supposedly the medical team was going to review them.

And they did.

And it was useless.

“Why were you high on February 22, 2003?”

Whether we were asking this question in March of 2003 or January of 2023 (almost 20 years later), the answer would be the same: I have no idea.

BG data, by itself, is like a single data point for a pilot. It’s useless without the contextual stream of data as well as other metrics (in the diabetes case, things like what was eaten, what activity happened, what my schedule was before this point, and all insulin dosed potentially in the last 12-24h).

So you wouldn’t be surprised to find out that I stopped tracking. I didn’t stop testing my blood glucose levels – in fact, I tested upwards of 14 times a day when I was in high school, because the real-time information was helpful. Retrospectively? Nope.

I didn’t start “tracking” things again (for diabetes) until late 2013, when we realized that I could get my CGM data off the device and into the laptop beside my bed, dragging the CGM data into a CSV file in Dropbox and sending it to the cloud so an app called “Pushover” would make a louder and different alarm on my phone to wake me up to overnight hypoglycemia. The only reason I added any manual “tracking” to this system was because we realized we could create an algorithm to USE the information I gave it (about what I was eating and the insulin I was taking) combined with the real-time CGM data to usefully predict glucose levels in the future. Predictions meant we could make *predictive* alarms, instead of solely having *reactive* alarms, which is what the status quo in diabetes has been for decades.

So sure, I started tracking what I was eating and dosing, but not really. I was hitting buttons to enter this information into the system because it was useful, again, in real time. I didn’t bother doing much with the data retrospectively. I did occasional do things like reflect on my changes in sensitivity after I got the norovirus, for example, but again this was mostly looking in awe at how the real-time functionality of autosensitivity, an algorithm feature we designed to adjust to real-time changes in sensitivity to insulin, dealt throughout the course of being sick.

At the beginning of 2020, my life changed. Not because of the pandemic (although also because of that), but because I began to have serious, very bothersome GI symptoms that dragged on throughout 2020 and 2021. I’ve written here about my experiences in eventually self-diagnosing (and confirming) that I have exocrine pancreatic insufficiency, and began taking pancreatic enzyme replacement therapy in January 2022.

What I haven’t yet done, though, is explain all my failed attempts at tracking things in 2020 and 2021. Or, not failed attempts, but where I started and stopped and why those tracking attempts weren’t useful.

Once I realized I had GI symptoms that weren’t going away, I tried writing down everything I ate. I tried writing in a list on my phone in spring of 2020. I couldn’t see any patterns. So I stopped.

A few months later, in summer of 2020, I tried again, this time using a digital spreadsheet so I could enter data from my phone or my computer. Again, after a few days, I still couldn’t see any patterns. So I stopped.

I made a third attempt to try to look at ingredients, rather than categories of food or individual food items. I came up with a short list of potential contenders, but repeated testing of consuming those ingredients didn’t do me any good. I stopped, again.

When I first went to the GI doctor in fall of 2020, one of the questions he asked was whether there was any pattern between my symptoms and what I was eating. “No,” I breathed out in a frustrated sigh. “I can’t find any patterns in what I’m eating and the symptoms.”

So we didn’t go down that rabbit hole.

At the start of 2021, though, I was sick and tired (of being sick and tired with GI symptoms for going on a year) and tried again. I decided that some of my “worst” symptoms happened after I consumed onions, so I tried removing obvious sources of onion from my diet. That evolved to onion and garlic, but I realized almost everything I ate also had onion powder or garlic powder, so I tried avoiding those. It helped, some. That then led me to research more, learn about the categorization of FODMAPs, and try a low-FODMAP diet in mid/fall 2021. That helped some.

Then I found out I actually had exocrine pancreatic insufficiency and it all made sense: what my symptoms were, why they were happening, and why the numerous previous tracking attempts were not successful.

You wouldn’t think I’d start tracking again, but I did. Although this time, finally, was different.

When I realized I had EPI, I learned that my body was no longer producing enough digestive enzymes to help my body digest fat, protein, and carbs. Because I’m a person with type 1 diabetes and have been correlating my insulin doses to my carbohydrate consumption for 20+ years, it seemed logical to me to track the amount of fat and protein in what I was eating, track my enzyme (PERT) dosing, and see if there were any correlations that indicated my doses needed to be more or less.

My spreadsheet involved recording the outcome of the previous day’s symptoms, and I had a section for entering multiple things that I ate throughout the day and the number of enzymes. I wrote a short description of my meal (“butter chicken” or “frozen pizza” or “chicken nuggets and veggies”), the estimate of fat and protein counts for the meal, and the number of enzymes I took for that meal. I had columns on the left that added up the total amount of fat and protein for the day, and the total number of enzymes.

It became very apparent to me – within two days – that the dose of the enzymes relative to the quantity of fat and protein I was eating mattered. I used this information to titrate (adjust) my enzyme dose and better match the enzymes to the amount of fat or protein I was eating. It was successful.

I kept writing down what I was eating, though.

In part, because it became a quick reference library to find the “counts” of a previous meal that I was duplicating, without having to re-do the burdensome math of adding up all the ingredients and counting them out for a typical portion size.

It also helped me see that within the first month, I was definitely improving, but not all the way – in terms of fully reducing and eliminating all of my symptoms. So I continued to use it to titrate my enzyme doses.

Then it helped me carefully work my way through re-adding food items and ingredients that I had been avoiding (like onions, apples, and pears) and proving to my brain that those were the result of enzyme insufficiency, not food intolerances. Once I had a working system for determining how to dose enzymes, it became a lot easier to see when I had slight symptoms from slightly getting my dosing wrong or majorly mis-estimating the fat and protein in what I was eating.

It provided me with a feedback loop that doesn’t really exist in EPI and GI conditions, and it was a daily, informative, real-time feedback loop.

As I reached the end of my first year of dosing with PERT, though, I was still using my spreadsheet. It surprised me, actually. Did I need to be using it? Not all the time. But the biggest reason I kept using it relates to how I often eat. I often look at an ‘entree’ for protein and then ‘build’ the rest of my meal around that, to help make sure I’m getting enough protein to fuel my ultrarunning endeavors. So I pick my entree/main thing I’m eating and put it in my spreadsheet under the fat and protein columns (=17 g of fat, =20 g of protein), for example, then decide what I’m going to eat to go with it. Say I add a bag of cheddar popcorn, so that becomes (=17+9 g of fat) and (=20+2 g of protein), and when I hit enter, those cells now tell me it’s 26 g of fat and 22 g of protein for the meal, which tells my brain (and I also tell the spreadsheet) that I’ll take 1 PERT pill for that. So I use the spreadsheet functionally to “build” what I’m eating and calculate the total grams of protein and fat; which helps me ‘calculate’ how much PERT to take (based on my previous titration efforts I know I can do up to 30g of fat and protein each in one PERT pill of the size of my prescription)

Example in my spreadsheet showing a meal and the in-progress data entry of entering the formula to add up two meal items' worth of fat and protein

Essentially, this has become a real-time calculator to add up the numbers every time I eat. Sure, I could do this in my head, but I’m usually multitasking and deciding what I want to eat and writing it down, doing something else, doing yet something else, then going to make my food and eat it. This helps me remember, between the time I decided – sometimes minutes, sometimes hours in advance of when I start eating and need to actually take the enzymes – what the counts are and what the PERT dosing needs to be.

I have done some neat retrospective analysis, of course – last year I had estimated that I took thousands of PERT pills (more on that here). I was able to do that not because it’s “fun” to track every pill that I swallow, but because I had, as a result of functional self-tracking of what I was eating to determine my PERT dosing for everything I ate, had a record of 99% of the enzyme pills that I took last year.

I do have some things that I’m no longer entering in my spreadsheet, which is why it’s only 99% of what I eat. There are some things like a quick snack where I grab it and the OTC enzymes to match without thought, and swallow the pills and eat the snack and don’t write it down. That maybe happens once a week. Generally, though, if I’m eating multiple things (like for a meal), then it’s incredibly useful in that moment to use my spreadsheet to add up all the counts to get my dosing right. If I don’t do that, my dosing is often off, and even a little bit “off” can cause uncomfortable and annoying symptoms the rest of the day, overnight, and into the next morning.

So, I have quite the incentive to use this spreadsheet to make sure that I get my dosing right. It’s functional: not for the perceived “fun” of writing things down.

It’s the same thing that happens when I run long runs. I need to fuel my runs, and fuel (food) means enzymes. Figuring out how many enzymes to dose as I’m running 6, 9, or 25 hours into a run gets increasingly harder. I found that what works for me is having a pre-built list of the fuel options; and a spreadsheet where I quickly on my phone open it and tap a drop down list to mark what I’m eating, and it pulls in the counts from the library and tells me how many enzymes to take for that fuel (which I’ve already pre-calculated).

It’s useful in real-time for helping me dose the right amount of enzymes for the fuel that I need and am taking every 30 minutes throughout my run. It’s also useful for helping me stay on top of my goal amounts of calories and sodium to make sure I’m fueling enough of the right things (for running in general), which is something that can be hard to do the longer I run. (More about this method and a template for anyone who wants to track similarly here.)

The TL;DR point of this is: I don’t track things for fun. I track things if and when they’re functionally useful, and primarily that is in real-time medical decision making.

These methods may not make sense to you, and don’t have to.

It may not be a method that works for you, or you may not have the situation that I’m in (T1D, Graves, celiac, and EPI – fun!) that necessitates these, or you may not have the goals that I have (ultrarunning). That’s ok!

But don’t say that you “couldn’t” do something. You ‘couldn’t’ track what you consumed when you ran or you ‘couldn’t’ write down what you were eating or you ‘couldn’t’ take that many pills or you ‘couldn’t’ inject insulin or…

You could, if you needed to, and if you decided it was the way that you could and would be able to achieve your goals.

How to Find the Slope of the Trend Line in A Graph With Google Sheets

I’ve been using Google Sheets for tracking and illustrating data for a number of reasons, such as tracking macronutrient and enzyme consumption patterns to help me understand my experiences with exocrine pancreatic insufficiency (EPI). It took me a while to figure out there was an easy way to display slope, though, to quantify what the trendline was.

Once you have a chart with your data, you can go into the “Customize” tab on the right and scroll down. Under “Series”, you can select which series you want, then scroll down and click “Trendline” to make the trendline appear. The customize menu then expands with trendline options.

Example of the default options for displaying a trendline

I had never noticed this before, but “Label” is set to default to “Custom”. This creates a label that defaults to “Trendline __YourSeriesName___”. In the example I’m showing here, I have series A labeled as “Var A”, so if I turn the Trendline on, it defaults to adding the “Custom” label of Trendline Var A.

But you can change this!

Click the dropdown where it says “Custom” and select “Use Equation”.

Example for trendline showing the label to 'use equation' option

Now it will show the label as the y=mx+b equation, so you can find out the slope (m) of your trendline.

In my example this means the slope of the Var A green line is 0.267.

You can modify this name, though, and get the best of both worlds. Click on the equation in the legend, and you will get an editable text box. I like to put the series name (e.g., Var A) in front of the equation so I can more easily see at a glance which series trend line it is explaining:

Example showing the custom text label after 'use equation' is selected to then edit and add back the series name along with the numeric formula

In my particular case, I want a quick glance of the slope, so I modify mine to read (Var A) 0.267 and (Var B) 0.061.

Example of trendline now showing the numeric equation and the series name for easier understanding of the graph

The only downside to this is the custom names will not automatically update. So if your brain can handle seeing the full mx+B equation, it might be better to leave it with the default equation as the trendline label name without modifying it at all, so it hopefully updates if you update the data on your graph. Otherwise, you’ll want to make a mental note to come back and update this manually by re-toggling the variable to equation and then editing it again to show the updated slope.

Two New Children’s Books – And How I Illustrated Them Without Being An Illustrator

I wrote two new books! You can find “Cooper’s Crutches” and “Chloe’s Cookies” on Amazon in paperback and Kindle formats.

Two children's books lay on the carpet: Cooper's Crutches and Chloe's Cookies, both written by Dana M. Lewis

One of these books I wrote years ago, about a month or so after I broke my ankle, inspired by the initial reactions from one of my nephews about me being on crutches. This new book is called Cooper’s Crutches.

I let it sit for several years, though, because I didn’t have illustrations for it. I’ve used a different illustrator or artist for each of my books so far.

A few weeks ago, though, I started thinking about experimenting with AI-driven illustrations for various projects, including wondering whether I could illustrate a children’s book or other projects with it.

The answer is: not yet. It’s hard to create a character who persists throughout image generation for enough scenes that can fit a two-dozen page storyline, although it would probably work for one or two images! (Especially if you managed to AI-illustrate a character that you could then place in various AI-illustrated scenes. The challenge is also having different poses for the same character, to illustrate a story.)

It then occurred to me to search around and I stumbled across a library of free, open source illustrations. Woohoo! Maybe those would work. Actually, I couldn’t even download that one due to a bug in their site, so I started searching (now that I knew to look for it) and found several other sets of illustrations. I even found a site called Blush that had a series of illustrations by various artists, and a web interface (GUI) that allowed you to modify images slightly then download them.

It’s like paper dolls, but digital – you can adjust the coloring of the hair, hair style, accessories, etc to modify the illustrated character.

I gave it a try, building some illustrations and downloading them. I then did some DIY-ing again in PowerPoint to modify them to help illustrate the full story in my children’s book. I printed a proof copy, but the versions I had downloaded for free were too low resolution and were fuzzy. However, the idea as a whole had worked great! I signed up for a free trial of the “Pro” version of Blush which enabled me to download both high-resolution PNG (image) files as well as SVG files.

Having SVG files theoretically would enable me to further modify and customize these, but as a non-illustrator even though I could load them in Figma and modify them, I still struggled to export them as high-enough resolution to work for printing in a book. I gave up and went back to DIY-ing the modifications in PowerPoint. They’re not perfect, but for the use case of my books (for a very small, niche audience), I doubt they care that they’re not perfect.

Here’s a selection of a few of the pages (not in order) in Cooper’s Crutches:

Excerpt images from Cooper's Crutches by Dana M. Lewis

At the same time that I started playing with these illustrations, I wondered whether I had any more ideas for books that I could illustrate at the same time with the same methods. I had had Cooper’s book written and waiting to illustrate; I now had a method to illustrate, but I wasn’t sure what story to illustrate.

But like all of my children’s books, inspiration again struck based on a situation and conversation I had with one of my nieces. She’s newly lactose intolerant and is taking lactase any time she has milk, like with milk and cookies for a bedtime snack. Lactase is an enzyme…and I’ve been taking enzymes of another sort this year, for exocrine pancreatic insufficiency.

Thus the next book, Chloe’s Cookies, was created!

Here’s a selection of a few of the pages (not in order) in the book:

Excerpt images from Chloe's Cookies, by Dana M. Lewis

Both Cooper’s Crutches and Chloe’s Cookies are illustrated with illustrations from a variety of artists who make their work available on Blush, including: Veronica Iezzi; Susana Salas; Pau Barbaro; Ivan Mesaroš; Mariana Gonzalez Vega; Deivid Saenz; and Cezar Berje.

The neat thing about Blush is their license: you can use the illustrations in any way, including commercial products, and you can modify or combine it with other works (like I did, modifying the images and combining illustrations from various artists) however you like.

I think I’ve likely maximized my use of Blush between these two books; unless other collections get uploaded in the future. But if you need a handful of illustrations that you can customize, definitely check it out!

And if you have ideas for other cool illustration libraries that I could use for future books, please let me know! (Or if you’re an artist who would like to contribute to one of my future books. :) )

TLDR:

I have two new children’s books, and you can find “Cooper’s Crutches” and “Chloe’s Cookies” on Amazon in paperback and Kindle formats.

Illustrating Children's Books without being an illustrator, plus introducting two new children's books by Dana M. Lewis

One Year of Pancreatic Enzyme Replacement Therapy for Exocrine Pancreatic Insufficiency (EPI or PEI)

I’ve had exocrine pancreatic insufficiency (EPI or PEI) for a full year now and have been taking pancreatic enzyme replacement therapy (PERT) ever since diagnosis.

I’ve written about what EPI is, what it’s like to go on PERT, and a variety of other posts (such as how I ultimately taught myself to titrate and adjust my dosing of PERT based on what I am eating) in the last year – you can see all my EPI posts listed at DIYPS.org/EPI. I also wrote recently about estimating the costs of PERT for a year, in which I had tallied up the number of PERT pills I had taken so far in the year. Since I’ve now hit the one year mark, I wanted to revisit that math.

In 365 days of pancreatic enzyme replacement therapy, I have consumed (at least) 3,277 pills.

That’s an average of 8.98 pills per day!

As I previously wrote, the number of pills is in part because I’m trying to lower the total costs (to everyone involved in paying for it) of my PERT by taking a mix of prescription PERT and OTC enzymes to try to balance effective dosing, cost, and the number of pills I swallow. I take one pill with my standard breakfast, so the remaining ~8 average pills are usually split between lunch, dinner, and/or a snack if I have one. (This is also influenced by my ultrarunning where I typically take ~2 pills every 30 minutes with my snacks/fuel for running, so long training days of 4 hours would involve 8 or more pills just for running fuel; obviously longer runs would involve even more, which drives the pills/day average higher.) If I wanted to reduce the total number of pills, I could by driving up the cost by using bigger, prescription PERT pills in lieu of some of the OTC options. However, most of the time, 3-4 pills per meal mixed between prescription and OTC is doable for me. I typically would choose to round up more PERT and reduce OTC pill count when I’m less certain about the macronutrient content of the meal or I want more confidence in better outcomes.

Speaking of better outcomes – is PERT effective?

For me, yes!

Overall, I feel so much better. Most of the time, I hardly ever have ANY symptoms (such as gas, bloating, or feeling icky) let alone my more extreme symptoms of “disrupting” my GI system. In the year of taking PERT, 78% of the time I had no disruption or any noticeable symptoms.

The average length of time between days with noticeable symptoms was 5.37 days.

And, if you look at the second half of the year, this increased quite a bit: 88% of the time I had no noticeable symptoms and the streak length of days between symptom days increased to 6.81 average days! The max streak is now 28 days (and counting)!

Showing the increasing length of streaks of consecutive days where I did not have any GI symptoms. The trend line shows a steady increase in the length of these streaks throughout the year.

That’s approaching a full month without any GI symptoms (woohoo) of any kind, and means less than 1 or 2 instances of symptoms per month for me in the last several months. That’s probably better than average for most people, even people without known GI conditions, and getting a lot closer back to my personal level of “normal”.

And obviously, this is continuing to increase over time as I improve my PERT dosing strategy.

This is pretty meaningful to think about.

PERT made a difference overall straight away, but I was also starting with very small portions of food and a very restricted diet. (This is because before I realized I had EPI I had done all kinds of behavioral gymnastics to try to eliminate foods like onion, garlic, and other foods that seemed to cause issues). So first I figured out PERT successfully for what I was eating; then carefully expanded my portion sizes back to typical quantities of food; then slowly expanded my diet to cover all the foods I used to eat before I started having all my GI problems.

It very much felt like I had three phases this year:

  • Phase 1: Use PERT to cover small quantities of small varieties of food. Figure out what foods I could eat that could “fit” into one PERT pill.
  • Phase 2: Start to figure out what quantities of food I wanted to eat, and get the PERT to match the food.
  • Phase 3: Finish expanding out my food choices to cover everything I was eating before and tackling all my “firsts” with PERT.

You can see this evolution in my diet, too, when you look at the relative changes in the amount of fat and protein I have eaten over the course of the year. (The one big obvious outlier on the graph in October is my 82 mile ultramarathon where I ate every 30 minutes for 25 hours!) There’s been a slight increase in my fat consumption over the course of the year, and protein consumption has stayed relatively flat as I’ve been making a very conscious effort to eat enough protein to fuel my ultrarunning endeavors throughout the year.

You can then see the relationship with increased number of pills (albeit pills with different amounts of lipase) over the course of the year, relative to the fat and protein consumed.

Displaying lines showing the relative amounts of fat and protein consumed throughout the year, plus the number of enzyme pills per day throughout 2022.

(Note that the pills per day is using a hidden right axis, whereas the fat and protein share the same left axis numbers, also not shown)

For anyone who is new (just diagnosed or recently diagnosed within a few weeks or months) to EPI, here’s what I would hope you take away:

  1. PERT works, but it needs to match what you are eating. Come up with a strategy (here’s mine – you can use it!) to adjust your dosing to match what you are eating. What you eat changes, and so should your PERT dosing.
  2. Things will improve over time, and you will get more effective at matching your dosing to what you are eating. You should be able to have more and more “streaks” of days without symptoms, or with reduced symptoms. However, this may take a few months, because you’ll likely also be – at the same time – re-expanding your variety of foods that you’re eating. The combination of eating more and different foods AND tweaking your dosing can make it take a little bit longer to figure it all out.
  3. If you’re not seeing success, talk with your doctor. There are different sizes of PERT pills – if you’re struggling to take X number of pills, you may be able to take fewer pills of a bigger size. There are different brands of PERT – so if one isn’t working for you (after you match your dosing to how much fat and protein is in each meal), you can switch and try another brand. There are also OTC options, which you can use to “top off” your prescription PERT or substitute, but you need to have an effective strategy for adjusting your dose that you can translate to your OTCs to be sure that they’re working.
One year of pancreatic enzyme replacement therapy for EPI by Dana M. Lewis

(PS – you can find my previous posts about EPI at DIYPS.org/EPI.)

Looking Back Through 2022 (What You May Have Missed)

I ended up writing a post last year recapping 2021, in part because I felt like I did hardly anything – which wasn’t true. In part, that was based on my body having a number of things going on that I didn’t know at the time. I figured those out in 2022 which made 2022 hard and also provided me with a sense of accomplishment as I tackled some of these new challenges.

For 2022, I have a very different feeling looking back on the entire year, which makes me so happy because it was night and day (different) compared to this time last year.

One major example? Exocrine Pancreatic Insufficiency.

I started taking enzymes (pancreatic enzyme replacement therapy, known as PERT) in early January. And they clearly worked, hooray!

I quickly realized that like insulin, PERT dosing needed to be based on the contents of my meals. I figured out how to effectively titrate for each meal and within a month or two was reliably dosing effectively with everything I was eating and drinking. And, I was writing and sharing my knowledge with others – you can see many of the posts I wrote collected at DIYPS.org/EPI.

I also designed and built an open source web calculator to help others figure out their ratios of lipase and fat and protease and protein to help them improve their dosing.

I even published a peer-reviewed journal article about EPI – submitted within 4 months of confirming that I had it! You can read that paper here with an analysis of glucose data from both before and after starting PERT. It’s a really neat example that I hope will pave the way for answering many questions we all have about how particular medications possibly affect glucose levels (instead of simply being warned that they “may cause hypoglycemia or hyperglycemia” which is vague and unhelpful.)

I also had my eyes opened to having another chronic disease that has very, very expensive medication with no generic medication option available (and OTCs may or may not work well). Here’s some of the math I did on the cost of living with EPI and diabetes (and celiac and Graves) for a year, in case you missed it.

Another other challenge+success was running (again), but with a 6 week forced break (ha) because I massively broke a toe in July 2022.

That was physically painful and frustrating for delaying my ultramarathon training.

I had been successfully figuring out how to run and fuel with enzymes for EPI; I even built a DIY macronutrient tracker and shared a template so others can use it. I ran a 50k with a river crossing in early June and was on track to target my 100 mile run in early fall.

However with the broken toe, I took the time off needed and carefully built back up, put a lot of planning into it, and made my attempt in late October instead.

I succeeded in running ~82 miles in ~25 hours, all in one go!

I am immensely proud of that run for so many reasons, some of which are general pride at the accomplishment and others are specific, including:

  • Doing something I didn’t think I could do which is running all day and all night without stopping
  • Doing this as a solo or “DIY” self-organized ultra
  • Eating every 30 minutes like clockwork, consuming enzymes (more than 92 pills!), which means 50 snacks consumed. No GI issues, either, which is remarkable even for an ultrarunner without EPI!
  • Generally figuring out all the plans and logistics needed to be able to handle such a run, especially when dealing with type 1 diabetes, celiac, EPI, and Graves
  • Not causing any injuries, and in fact recovering remarkably fast which shows how effective my training and ‘race’ strategy were.

On top of this all, I achieved my biggest-ever running year, with more than 1,333 miles run this year. This is 300+ more than my previous best from last year which was the first time I crossed 1,000 miles in a year.

Professionally, I did quite a lot of miscellaneous writing, research, and other activities.

I spent a lot of time doing research. I also peer reviewed more than 24 papers for academic journals. I was asked to join an editorial board for a journal. I served on 2 grant review committees/programs.

I also wrote a lot.

*by ton, I mean way more than the past couple of years combined. Some of that has been due to getting some energy back once I’ve fixed missing enzyme and mis-adjusted hormone levels in my body! I’m up to 40+ blog posts this year.

And personally, the punches felt like they kept coming, because this year we also found out that I have Graves’ disease, taking my chronic disease count up to 4. Argh. (T1D, celiac, EPI, and now Graves’, for those curious about my list.)

My experience with Graves’ has included symptoms of subclinical hyperthyroidism (although my T3 and T4 are in range), and I have chosen to try thyroid medication in order to manage the really bothersome Graves’-related eye symptoms. That’s been an ongoing process and the symptoms of this have been up and down a number of times as I went on medication, reduced medication levels, etc.

What I’ve learned from my experience with both EPI and Graves’ in the same year is that there are some huge gaps in medical knowledge around how these things actually work and how to use real-world data (whether patient-recorded data or wearable-tracked data) to help with diagnosis, treatment (including medication titration), etc. So the upside to this is I have quite a few new projects and articles coming to fruition to help tackle some of the gaps that I fell into or spotted this year.

And that’s why I’m feeling optimistic, and like I accomplished quite a bit more in 2022 than in 2021. Some of it is the satisfaction of knowing the core two reasons why the previous year felt so physically bad; hopefully no more unsolved mysteries or additional chronic diseases will pop up in the next few years. Yet some of it is also the satisfaction of solving problems and creating solutions that I’m uniquely poised, due to my past experiences and skillsets, to solve. That feels good, and it feels good as always to get to channel my experiences and expertise to try to create solutions with words or code or research to help other people.

How To Dose Pancreatic Enzyme Replacement Therapy (PERT) By What You Are Eating – And A Free Web Calculator To Calculate Enzyme Dosing

I’ve had exocrine pancreatic insufficiency (known as EPI or PEI) for a year now. I have had type 1 diabetes for 20+ years and am experienced in adjusting my medication (previously insulin) in response to everything that I eat or drink.

With EPI, though, most people are given a static prescription, such as one saying “take 3 pills with each meal”.

Well, what if every meal is not the same size?

Let’s think about a couple of hypothetical meals.

Meal A: Baked chicken, sweet potato, and broccoli. This meal likely results in ~31 grams of carbohydrates; 7 grams of fat; and ~30 grams of protein.

How would you dose for this meal? Most people do what they are told and dose based on the fat content of the meal. If they typically take 3 pills, they may take all 3 pills or take fewer pills if this is less fat than their typical meal.

Many people post in EPI social media groups post about restaurant dinners that sound like this complaining about side effects they experience with this type of meal. The commonly mentioned theory is that maybe the chicken is cooked in oil. However, the entire meal is so low in fat compared to other meals that it is unlikely to be the fat content causing symptoms if the typical meal dose of PERT is used, even if the chicken is cooked in oil.

Let’s discuss another meal.

Meal B: A bowl of chili topped with cheddar cheese and a piece of cornbread.

This meal results in ~45 grams of carbs; ~30 grams of fat; and ~42 grams of protein.

The fat content between these two meals is quite a bit different (7 grams of fat versus 30 grams of fat). Yet, again, most people are told simply to dose by the amount of fat, so someone might take a lower dose for the chicken meal because it has so little fat relative to other meals.

This could result in symptoms, though. The pancreas actually produces THREE kinds of enzymes. That’s why pancreatic enzyme replacement therapy medicine, called pancrelipase as a common name, has THREE types of enzymes: lipase, to help digest fat; protease, to help digest protein; and amylase, to help digest carbohydrates. A typical PERT pill has different amounts of these three enzymes, although it is usually described by the size/quantity of lipase it has – yet the other enzymes still play an important role in digestion.

I’ve observed that it’s pretty common for people to completely ignore the protein in what they’re eating. But as I mentioned, that seems to be the most obvious thing to try dosing for if “low fat” meals are causing issues. (It could also be sensitivity to carbohydrates, but the above example meal is fairly low carbohydrate.) My personal experience has also been that I am sensitive to fat and protein, and dose my meals based on these macronutrients, but other than eating fruit on an empty stomach (when I would add PERT/enzyme, despite the zero fat and protein in it), I don’t need to dose based on carbohydrates.

But I do need to dose for BOTH fat AND protein in what I’m eating. And I have a theory that a lot of other people with EPI do, too.

So how do you do this?

How do you dose for meals of different sizes, and take into account both fat and protein for these varying meals?

First, you need to figure out what dosing “works” for you and begin to estimate some “ratios” that you can use.

Most people begin experimenting and find a quantity of food that they can eat with the dose that they typically take. This meal size is going to vary person to person; it’ll also vary based on what it is in the meal they’re eating (such as chicken vs chili, from the above examples).

Once you find a dose that “works” and try it out a few times on the same meal, you can use this to determine what your ratios/dosing should be.

How?

Let’s use two examples with different dose sizes and types of PERT.

(PS – did you know there are 6 FDA-approved PERT brands in the US? Sometimes one works for someone where a different brand does not. If you’re struggling with the first type of PERT you’ve been prescribed, and you’ve already ruled out that you’re dosing correctly (see below), make sure to talk to your doctor and ask about trying a different brand.)

First, let’s calculate the ratios of lipase needed per gram of fat.

Let’s say the meal that “works” with your typical dose is 30 grams of fat. If 30 grams of fat is fine on your current dose, I would eat another meal with a slightly higher amount of fat (such as 35 or 40 grams of fat). When you get to an amount that “doesn’t work” – meaning you get symptoms – then you go back to the dose that does “work” to use in the math.

If the meal that “worked” was 30 grams of fat I would do the following math for each of these two examples:

Example A: You need 1 pill of Zenpep 25,000 to cover this meal

Example B: You need 3 pills of Creon 36,000 to cover this meal

Example A: 1 pill of Zenpep 25,000 is 1 multiplied by 25,000, or 25,000 units of lipase. Take that (25,000) and divide it by the grams of fat in the meal that works (30 grams). This would be 25,000/30 = 833. This means you need 833 units of lipase to “cover” 1 gram of fat. You can round up to ~1000 units of lipase to make it easier; your ratio would be 1000 units of lipase for every 1 gram of fat.

Example B: 3 pills of Creon 36,000 is 3 multiplied by 36,000, which is 108,000 units of lipase. Take that number (108,000) and divide it by the grams of fat in the meal that works (30 grams). This would be 108,000/30 = 3,600. This means you need 3,600 units of lipase to “cover” 1 gram of fat.

The next time you wanted to eat a meal, you would look at the grams of fat in a meal.

Let’s say you’re going to eat two bowls of chili and two pieces of cornbread. Let’s assume that is about 64 grams of fat. (Two bowls of chili and two cornbread is 30×2=60, plus a bit of butter for the cornbread so we’re calling it 64 grams of fat).

Example A: Take the meal and multiply it by your ratio. 64 (grams of fat) x 1,000 (how many units of lipase you need to cover 1 grant of fat) = 64,000. A Zenpep 25,000 has 25,000 lipase. Since you need 64,000 (units of lipase needed to cover the meal), you would divide it by your pill/dose size of 25,000. 64,000 divided by 25,000 is 2.56. That means for these ratios and a prescription of Zenpep 25,000 pill size, you need *3* Zenpep 25,000 to cover a meal of 64g of fat. (Remember, you can’t cut a PERT, so you have to round up to the next pill size.)

Example B: Take the meal and multiply it by your ratio. 64 (grams of fat) times 3,600 (how many units of lipase you need to cover 1 grant of fat) = 230,400. A Creon 36,000 has 36,000 lipase. Since you need 230,400 units of lipase to cover the meal, you would divide it by your pill/dose size of 36,000. 230,400 divided by 36,000 is 6.4. This means you need *7* Creon 36,000 to cover a meal of 64g of fat. (Again, you can’t cut a PERT, so you have to round up to 7 from 6.4.)

Another way to think about this and make it easier in the future is to determine how much one pill “covers”.

Example A: A Zenpep 25,000 “covers” 25 grams of fat if my ratio is 1000 units of lipase for every gram of fat (25,000/1000=25).

So if a meal is under 25g of fat? 1 pill. A meal under 50g (25×2)? 2 pills. 75g (25×3)? 3 pills. And so on. Once you know what a pill “covers”, it’s a little easier; you can simply assess whether a meal is above/below your pill size of 1 (25g), 2 (50g), 3 (75g) etc.

Example B: A Creon 36,000 “covers” 10 grams of fat if my ratio is 3,600 units of lipase for every gram of fat (36,000/3600=10).

So if a meal is under 10 grams of fat? 1 pill. 20 grams of fat is 2 pills (10×2); 30 grams of fat is 3 pills (10×3); etc.

When people with EPI share experiences online, they often describe their dose size (such as 1 x 25,000 or 3 x 36,000 like examples A and B above) for most meals, but the meal size and composition is rarely discussed.

Personally, I can eat pretty widely varying amounts of fat in each meal on a day to day basis.

That’s why, instead of a flat dosing that works for everything (because I would be taking a LOT of pills at every meal if I was trying to take enough to cover my highest fat meals every time), I have found it to be more effective to estimate each meal to determine my meal dosing.

Remember that meal estimates aren’t very precise. If you use a nutrition panel on a box serving, the serving size can vary a bit. Restaurants (especially chains) have nutrition information, but the serving size can vary. So recognize that if you are calculating or estimating 59 grams of fat and that means either 2 vs 3 pills or 6 vs 7 pills, that you should use your judgment on whether you want to round up to the next pill number – or not.

Let’s put the hypothetical meals side by side and compare dosing with examples A and B from above:

Example of how much PERT is needed for two different meals based on dose ratios from Examples A and B

Using the previous meal examples with either 7 or 30 grams of fat:

  • With Example A (ratio of 25g of fat for every 1 pill, or 1000 units of lipase to cover 1 gram of fat), we would need 1 pill for the chicken meal and 2 for the chili meal. Why? The chili is >25 grams of fat which means we need to round up to 2 pills.
  • With Example B (ratio of 10 grams of fat for every 1 pill or 3600 units of lipase to cover 1 gram of fat), we would need 1 pill to cover the chicken (because it’s less than 10 grams of fat) and 3 – or more – pills for the chili. Why “or more”? Well, something like chili is likely to be imprecisely counted – and if you’re like me, you’d want a bit of extra cheese, so chances are I would round up to a 4th pill here to take in the imprecision of the measurements of the ingredients.

PERT Dosing for Protein

Wait, didn’t you say something about protein?

Yes, I did. Fat isn’t the only determinant in this math!

I do the same type of math with grams of protein and units of protease. (Remember, PERT has all 3 types of enzymes, even though it is labeled by the amount of lipase. You can look online or on the bottle label to see how much protease is in your PERT.)

For our examples, Zenpep 25,000 contains 85,000 units of protease. Creon 36,000 contains 114,000 units of protease.

For the meal that ‘worked’ of 30 grams of fat, we also want to know the protein that worked. For easy math, let’s also say 30 grams of protein is in this meal.

Following the same math as before:

Example A (Zenpep 25,000): 30 grams of protein divided by 1×85,000 units of protease is ~2,833 units of protease to every 1 gram of protein. Again, I like to think about how much 1 pill “covers” protein-wise. In this case, 1 Zenpep 25,000 “covers” 30 grams of protein.

Example B (Creon 36,000): 30 grams of protein divided into 3 x 114,000 units of protease is 11,400 units of protease per gram of protein. Again, I like to think about how much 1 pill “covers” protein-wise as well. In this case, 1 Creon 36,000 “covers” 10 grams of protein.

Here’s how many pills are needed for protein:

Example of how much PERT is needed for two different meals based on dose ratios from Examples A and B, showing both protein and fat quantities

  • With Example A (ratio of 30g of protein for every 1 pill), we would need 1 pill for the chicken meal and 2 for the chili meal. Why? The chili is 42, which is greater than (30×1) grams of protein which means we need to round up to 2 pills.
  • With Example B (ratio of 10 grams of protein for every 1 pill), we would need 3 or more pills to cover the chicken. Why 3 or more? Again, it’s on the top edge of what 3 pills would cover, so I’d be likely to round up to 4 pills here. For the chili, 5 pills are needed (42 is more than 4 x 10 and is less than 5 x 10).

So how do you decide the number of pills to take for these meals? Let’s go back to our two example meals and compare the amount needed, pill-wise, for both fat and protein for each meal and each example.

Example of how much PERT is needed for two different meals based on dose ratios from Examples A and B and comparing the number of pills for fat and protein

When the pill numbers MATCH (e.g. the same number needed for fat and protein), which is the case for both examples with Zenpep 25,000, it’s easy: take that number of pills total! For Zenpep 25,000, I would take 1 pill for the Chicken (1 fat | 1 protein); and I would take 2 pills for the Chili (2 fat | 2 protein). Remember that PERT pills contain all three enzymes, so the fat and protein are sufficiently *each* covered by the quantities of lipase and protease in this pill type.

When the pill numbers are DIFFERENT between your fat and protein estimates, you use the LARGER number of pills. For Creon 36,000, with the chicken meal the protein quantity is much larger than the fat quantity; I would in this case dose 4 total pills (1 fat | 4 protein), which would then cover the protein in this meal and would also sufficiently cover the amount of fat in this meal. For the chili meal, it is closer: I estimated needing 4 pills for fat and 5 for protein; in this case, I would take 5 total pills which would then successfully cover the protein and the fat in the meal.

If you find the math challenging to do, don’t worry: once you determine your ratios and figure out how much one pill “covers”, it gets a lot easier.

And I made a tool to help you!

Check out this free enzyme calculator which does the math to determine the ratios on exactly how much one pill “covers” for your successful meal.

Here’s what it looks like using the two examples above:

Example of Part 1 of the EPI Enzyme Calculator using Zenpep 25,000, where 1 pill covers 30 grams of fat and 30 grams of protein. Example of Part 1 of the EPI Enzyme Calculator using Creon 36,000, where 3 pills covers 30 grams of fat and 30 grams of protein.

You can input your meal that “works”, what your dose is that “works” (the number of pills and pill type), and it will share what your ratios are and what one pill “covers”.

You can also use the second part of the calculator to estimate the amount you need for a future meal! Say it’s coming up on a holiday and you’re going to eat a much larger meal than you normally do.

You can input into the calculator that you’ll be eating 90 grams of fat and 75 grams of protein.

Here’s the example with our dose from Example A (Zenpep 25,000):

Example of Part 2 of the EPI Enzyme Calculator using Zenpep 25,000, with a future larger meal of 90 grams of fat and 75 grams of protein.

Here’s the example large meal with our dose from Example B (Creon 36,000):

Example of Part 2 of the EPI Enzyme Calculator using Creon 36,000, with a future larger meal of 90 grams of fat and 75 grams of protein.

You can also hit the button to expand the calculations to see the math it is doing, and how it compares between the fat and protein pill estimates to see what “drives” the total number of pills needed.

You can also hit the button to expand the calculations to see the math it is doing, and how it compares between the fat and protein pill estimates to see what “drives” the total number of pills needed, with the calculation view expanded to show all the details

You can even download a PDF with this math to have on hand. Here’s what the PDF download looks like for Example B (Creon 36,000):

Example of a PDF print view of the same data from previous screenshots with a Creon 36000 example

Switching dose sizes or PERT brand types

This calculator can also be useful if you were originally prescribed a smaller quantity of PERT (e.g. Creon 3000 or Zenpep 3000) and you find yourself taking many numbers of these pills (6 or more) to cover a small meal for you, let alone more pills for a larger meal.

You can input this into the calculator and get your ratios; then in the second part, identify a different pill size, to see how many numbers of pills you’d take on a different dose.

Example switching from one size of PERT pill to another size

You can also use it to help you understand how much you might need if you are switching between brands. Let’s say you were prescribed Zenpep 25,000 and you need to try Creon, either because you don’t think Zenpep works well for you or your insurance is more willing to cover the Creon brand.

You would use the top part of the calculator with your current brand and size (e.g. Zenpep 25,000 of which you take 6 for a standard meal of 30 grams of fat and 30 grams of protein) and then input the new brand and size and the same size meal (e.g. Creon 36,000 and another 30 grams of fat and 30 grams of protein meal) to see that you’d likely need 5 Creon 36,000 to match the 6 Zenpep 25,000 you were taking for a standard size (30 gram of fat and 30 gram of protein) meal.

Example of using the calculator to estimate the different number of pills for a different brand and size of PERT pill

Note: I’m not suggesting 30 grams of fat and protein at each meal is “standard” or the “right” size of the meal – I picked arbitrary numbers here to illustrate these examples, so make sure to include the meal that your PERT dosing successfully covers for YOU!

As a reminder, I’m not a doctor – I’m a person living with EPI. None of this is medical advice. I use this math and this calculator for my own personal use and share it in case it’s helpful to others. If you have questions, please do talk to your doctor. If you’re still experiencing symptoms with your enzyme dosing, you definitely should talk with your doctor. Your prescription size might need updating compared to what you were originally prescribed.

Also, please note that the calculator is open source; you can find the code here, and I welcome contributions (pull requests) and suggestions! You can leave feedback on Github or share feedback in this form. For example, if you’re using a different type of enzyme not listed in the calculator (currently 2/6 of the US FDA-approved versions are listed), please let me know and I can work to add the relevant list.

PS – You can find my other posts about EPI at DIYPS.org/EPI.

More Tools To Help Diabetes Researchers and Other Researchers

A few years ago I made a big deal about a tool I had created, converting someone’s web tool into a command line tool to be able to take complex json data and convert it to csv. Years later, I (and thousands of others, it’s been downloaded 1600+ times!) am still using this tool because there’s nothing better that I’ve found when you have data that you don’t know the data structure for or the data structure varies across files.

I ended up creating a repository on Github to store it with details on running it, and have expanded it over the last (almost) six years as I and others have added additional tools. For example, it’s where Arsalan, one of my frequent collaborators, and I store open source code from some of our recent papers.

Recently, I added two more small scripts. This was motivated to help researchers who have been successfully using the OpenAPS Data Commons and want to update their dataset with a later version of the data. Chances are, they have cleaned and worked with a previous version of the dataset, and instead of having to re-clean all of the data all over again, this set of scripts should help narrow down what the “new” data is that needs to be pulled out, cleaned, and appended to a previously cleaned dataset.

You can check out the full tool repository here (it has several other scripts in addition to the ones mentioned above). The latest are two python scripts that checks the content of an existing folder and lists out the memberID and filenames for each. This is useful to run on an existing, already-cleaned dataset to see what you currently have. It can also be run on the latest/newest/bigger dataset available. Then, the second script can be run to compare the memberIDs and file names in the newer/biggest/larger dataset against the previously cleaned/smaller/older dataset. Those that “match” already exist in the version of the dataset they have; they don’t need to be pulled again. The others don’t exist in the current dataset, and can be popped into a script to pull out just those data files to then be cleaned and appended to the existing dataset.

As a heads up specifically for those working with the OpenAPS Data Commons, it is best practice to name/describe the version of the dataset via the size. For example, you might be working with the n=88 or n=122 version of the dataset. If you used the above method, you would then describe it along the lines of taking and cleaning the n=122 version; selecting new files available from the n=183 version and appending them to the n=122 version; and the resulting dataset is n=(122+number of new files used).

Folks who access the n=183 version of the dataset and haven’t previously used a smaller version of the dataset can reference using the n=183 and clarifying how many files they ended up using, e.g. describing that they followed X method to clean the data starting from the n=183 version and their resulting dataset is n=166, for example.

It is important to clarify which version and size of the dataset is being used.

PS – this method works on other data file types, too! You’d change the variable/column header names in the script to update this for other cases.

We Have Changed the Standards of Care for People With Diabetes

We’ve helped change the standard of care for people with diabetes, with open source automated insulin delivery.

I get citation alerts sometimes when my previous research papers or articles are cited. For the last few years, I get notifications when new consensus guidelines or research comes out that reference or include mention of open source automated insulin delivery (AID). At this time of year, the ADA Standards of Care is released for the following year, and I find out usually via these citation alerts.

Why?

This year, in 2023, there’s a section on open source automated insulin delivery!

A screenshot of the 2023 ADA Standards of Care section under Diabetes Technology (7) that lists DIY closed looping, meaning open source automated insulin delivery

But did you know, that’s not really new? Here’s what the 2022 version said:

A screenshot of the 2022 ADA Standards of Care section under Diabetes Technology (7) that lists DIY closed looping, meaning open source automated insulin delivery

And 2021 also included…

A screenshot of the 2021 ADA Standards of Care section under Diabetes Technology (7) that lists DIY closed looping, meaning open source automated insulin delivery

And 2020? Yup, it was there, too.

A screenshot of the 2020 ADA Standards of Care section under Diabetes Technology (7) that lists DIY closed looping, meaning open source automated insulin delivery

All the way back to 2019!

A screenshot of the 2019 ADA Standards of Care under Diabetes Technology (7) that lists DIY closed looping, meaning open source automated insulin delivery

If you read them in chronological order, you can see quite a shift.

In 2019, it was a single sentence noting their existence under a sub-heading of “Future Systems” under AID. In 2020, the content graduated to a full paragraph at the end of the AID section (that year just called “sensor-augmented pumps”). In 2021, it was the same paragraph under the AID section heading. 2022 was the year it graduated to having its own heading calling it out, with a specific evidence based recommendation! 2023 is basically the same as 2022.

So what does it say?

It points out patients are using open source AID (which they highlight as do-it-yourself closed loop systems). It sort of incorrectly suggests healthcare professionals can’t prescribe these systems (they can, actually – providers can prescribe all kinds of things that are off-label – there’s just not much point of a “prescription” unless it’s needed for a person’s elementary school (or similar) who has a policy to only support “prescribed” devices).

And then, most importantly, it points out that regardless, healthcare providers should assist in diabetes management and support patient choice to ensure the safety of people with diabetes. YAY!

“…it is crucial to keep people with diabetes safe if they are using these methods for automated insulin delivery. Part of this entails ensuring people have a backup plan in case of pump failure. Additionally, in most DIY systems, insulin doses are adjusted based on the pump settings for basal rates, carbohydrate ratios, correction doses, and insulin activity. Therefore, these settings can be evaluated and modified based on the individual’s insulin requirements.”

You’ll notice they call out having a backup plan in case of pump failure.

Well, yeah.

That should be true of *any* AID system or standalone insulin pump. This highlights that the needs of people using open source AID in terms of healthcare support are not that different from people choosing other types of diabetes therapies and technologies.

It is really meaningful that they are specifically calling out supporting people living with diabetes. Regardless of technology choices, people with diabetes should be supported by their healthcare providers. Full stop. This is highlighted and increasingly emphasized, thanks to the movement of individuals using open source automated insulin delivery. But the benefits of this is not limited to those of us using open source automated insulin delivery; this spills over and expands to people using different types of BG meters, CGM, insulin pumps, insulin pens, syringes, etc.

No matter their choice of tools or technologies, people with diabetes SHOULD be supported in THEIR choices. Not choices limited by healthcare providers, who might only suggest specific tools that they (healthcare providers) have been trained on or are familiar with – but the choices of the patient.

In future years, I expect the ADA Standard of Care for 2024 and beyond to evolve, in respect to the section on open source automated insulin delivery.

The evidence grading should increase from “E” (which stands for “Expert consensus or clinical experience”), because there is now a full randomized control trial in the New England Journal of Medicine on open source automated insulin delivery, in addition to the continuation results (24 weeks following the RCT; 48 full weeks of data) accepted for publication (presented at EASD 2022), and a myriad of other studies ranging from retrospective to prospective trials. The evidence is out there, so I expect that this evidence grading and the text of the recommendation text will evolve accordingly to catch up to the evidence that exists. (The standards of care are based on literature available up to the middle of the previous year; much of the things I’ve cited above came out in later 2022, so it matches the methodology to not be included until the following year; these newest articles should be scooped up by searches up to July 2023 for the 2024 edition.)

In the meantime, I wish more people with diabetes were aware of the Standards of Care and could use them in discussion with providers who may not be as happy with their choices. (That’s part of the reason I wrote this post!)

I also wish we patients didn’t have to be aware of this and don’t have to argue our cases for support of our choices from healthcare providers.

But hopefully over time, this paradigm of supporting patient choice will continue to grow in the culture of healthcare providers and truly become the standard of care for everyone, without any personal advocacy required.

Did you know? We helped change the standards of care for people living with diabetes. By Dana M. Lewis from DIYPS.org

Adding Lines On A Google Sheets Chart To Indicate Today’s Date And Other Dates

Filed under more micro hacks that I’ve been doing lately (see this script I wrote to flag embedded social media content so that I could switch it to an image instead), I have been building time series charts to track various things.

One thing I was doing was exporting the finished chart to an image, then manually adding a line to mark dates of interest on the chart.

Then I realized that I could insert lines automatically to reflect dates, without having to manually add it to the exported image.

How?

First, I added another column to my sheet. I created a value in that column on the date of interest (my date is in the adjacent column). For this particular chart, I had made the data points I was tracking by date as “1”, so to show a different size line I made the date of interest 1.2. I changed the min and max values to be 0.7 and 1.2, which means that the “1”s showed up in the middle of the graph and my “date” markers went the full height of the graph.

Here’s how the source data looks in my sheet:

Example of a spreadsheet with 4 columns. The first two columns have 1s as values to indicate tracking; the second column has a single 1.20 value shown next to a relevant date; the fourth column is a list of dates

Then, I expanded my chart to include the new line as a data source, and my chart then looked something like this:

Example of a generated chart with a date line displayed on top of the tracked data.

Because I am simply tracking the presence of things, I’ve hidden the left horizontal axis, because the value is not a meaningful data point and just an artifact of the numbers I’ve chosen to visualize the data on particular days. (Again, what’s displayed has a min of 0.7 and max of 1.2, so the blue and green lines have 1 values whereas the purple line I’m using to indicate a major date is a value of 1.2)

That’s a fixed date, though. I want to track data over time and be able to have the graph automatically update without me having to constantly expand the series of data the chart includes. I’d rather include a month or two of empty data in advance, and have today’s date flagged.

But that’s not a default feature, so how could I make this work? With a similar trick of graphing the date, but using a feature of Google Sheets where you can enter “=TODAY()” and have the cell fill with today’s value. It automatically updates, so it can therefore shift along my graph as long as I’ve gotten a sufficiently large selection of data past today’s date.

I struggled with having a single cell value selected, though, so I ended up creating another column. In this column, I had it check for what today’s date was (TODAY()) and compare it against the date in my existing date column. If the date matched today’s date, then it would display a 1.2 value. If it didn’t match, it would leave the cell blank. The full formula for this was:

=IF(TODAY()=D3,1.2, )

This checked if my date column (column D for me; make sure to update with the column letter that matches where your dates live) had today’s date and marked it if so.

It worked! Here’s how it looks – I’ve made the today’s date marker a different color (bright orange) than my other dates of interest (purple):

Example chart with the relevant date line shown and later, a date line (in a different color) distinguishing today's date. This chart will auto-update based on the method described in the full text of the blog post

This orange line will keep shifting to today’s date, so I can quickly glance at this chart and not have to be updating the data selection of the chart as often.

Troubleshooting tips:

I ran into a couple of errors. First, I had used quotes around the 1.2 value in my formula, which entered it as text so it wouldn’t graph the line on my chart. Removing the “” in the formula (correctly written above) changed it back to number formatting so it would graph. Also, I had selected a smaller portion of data for this chart, but then I grabbed the entire today’s date column, so the today’s-date line was incorrectly graphed at the far right of the graph rather than on today’s date. That was because of the size of data mismatch; I had something like A334-C360,G3:G360. Instead, I had to make sure the today’s-date checking column matched the size of the other data selection, meaning A334-C360,G334-360 (notice how the first G number is now updated to match the A starting number). So, if you see your value graphed in an unexpected place, check for that.

Other tricks

PS – I actually am getting my “1” values based on data from another tracking spreadsheet. I use a formula to check for the presence of cell values on another tab where I am simply marking with an ‘x’ or various other simple markers. I use another IF checking formula to see if the cell matching the date in the other tab has a value, and if so, printing that 1 value I illustrated in my source data.

The formula I use for that is:

=IF(OTHER-TAB-NAME!E3=””,,1)

It checks to see if the cell (E3) in the other tab for the same date row has a value. If so, it marks a 1 down and otherwise leaves it blank. That way I can create these rows of values for graphing and additional elements, like the today’s-date row, in another sheet without getting in the way of my actual tracking sheet.

Replacing Embedded Tweets With Images

If you’re like me, you may have been thrilled when (back in the day) it became possible to embed public social media posts such as tweets on websites and blogs. It enabled people who read here to pop over to related Twitter discussions or see images more easily.

However, with how things have been progressing (PS – you can find me @DanaMLewis@med-mastodon.com as well), it’s increasingly possible that a social media account could get suspended/banned/taken down arbitrarily for things that are retrospectively against new policies. It occurred to me that one of the downsides to this is the impact it would have on embedded post content here on my blog, so I started thinking through how I could replace the live embedded links with screenshots of the content.

There’s no automatic way to do this, but the most efficient method that I’ve decided on has been the following:

1 ) Export an XML file of your blog/site content.

If you use WordPress, there’s an “Export” option under “Tools”. You can export all content, it doesn’t matter.

2 ) Run a script (that I wrote with the help of ChatGPT).

I called my script “embedded-links.sh” and it searches the XML file for URLs found between “[ embed ]” and “[\embed]” and generates a CSV file. Opening the CSV with Excel, I can then see the list of every embedded tweet throughout the site.

I originally was going to have the script pair the embedded links (twitter URLs) to the post it was found within to make it easier to go swap them out with images, but realized I didn’t need this.

(See no. 4 for more on why not and the alternative).

3 ) I created screenshots with the URLs in my file.

I went through and pasted each URL (only about 60, thankfully) into https://htmlcsstoimage.com/examples/twitter-tweet-screenshot’s example HTML code and then clicked “re-generate image” in the top right corner under the image tab. Then, I right-clicked the image and chose “Save As” and saved it to a folder. I made sure to rename the image file as I saved it each time descriptively; this is handy for the next step.

I did hit the free demo limit on that tool after about 30 images, and I had 60, so after about 20 minutes I went back and checked and was able to do my second batch of tweets.

(There are several types of these screenshot generators you could use, this one happened to be quick and easy for my use case.)

4 ) I then opened up my blog and grabbed the first link and pasted it into the search box on the Posts page.

It pulled up the list of blog posts that had that URL.

I opened the blog post, scrolled to the embedded tweet, deleted it, and replaced it with the image instead.

(Remember to write alt text for your image during this step!)

Remember to ‘update’/save your post, too, after you input the image.

It took maybe half an hour to do the final step, and maybe 2-3 hours total including all the time I spent working on the script in number 2, so if you have a similar ~60 or so links I would expect this to take ~1-2 focused hours.

Replacing embedded web content with images by Dana M. Lewis