Category: Diabetes

A couple days ago I posted my diabetes spreadsheet, in case anybody finds it useful. This post is a continuation, so if you are not interested, please save your time and skip this post.

If you are interested, and you missed the other post, it is here, along with a link to the spreadsheet itself.

I said the other day that I would post about the second tab, so here goes (it’s easier than the last post):

If you open the spreadsheet in Excel, and look along the bottom, you see two tabs. The default tab is called Data, which we discussed last time. The second tab is called Calculations. This tab contains data which the macro uses to make its calculation. So straight away, if you’re not interested, you can just leave this tab alone.

If you are interested, though, I will explain:

The spreadsheet supports either of the two units worldwide, mmol/l and mg/dl. In fact, the two are related by a simple formula, just like, say, C and F. The two are related such that:

1 mmol/l = 18 mg/dl

in other words:

1 mg/dl = 1/18 mmol/l

So the first thing you see is a table of possible units, alongside the factor needed to get from one unit to the other. 1/18 = 0.055556. If you change these numbers, you will mess up the calculation. This data is cells B2:C3, on the Calculations tab.

The second item on this tab is just a reminder of what primary units you have selected. We talked about primary units last time, so I won’t drone on again. In practise, this value is just copied from the previous tab. This is cell B6 on the Calculations tab.

The last thing on the Calculations tab is a kind-of key. When the macro fires, it ends up reading data in certain columns, and writing some data into different columns. The macro needs to know, therefore, what column to read/write such-and-such a piece of data from/to. On the Calculations tab, the cells D9:D17 define these columns.

In that way, you can add your own columns to the spreadsheet, if you want, just so long as you update these cells too.

In the same vein, the way the macro works is to look back fifty days, and work out what the first cell is that it needs to include. To do this, it starts off at one row, then looks back at the previous row, then the previous row, and so on. This approach allows you to have as few or as many entries as you like. But by looking back one row at a time, there is a danger that the macro will count beyond the end of the data. So, you need to tell it where the end of the data is.

On the Calculations tab, Cell D18 tells the macro which the first row is that holds data. In the initial case, it is Row 5. But again, you can add your own rows (or remove mine) to the top of the spreadsheet and, so long as you update this cell, the calculation will still work.

Lastly, you know I mentioned that averages were calculated over the last fifty days? Well, the reason I chose fifty was just that it seemed like a decent length of time, but not including dates which are so far back, they might not be relevant. In fact, when you have your HBA1C blood test, this is also an average over the last couple of months.

But, if you have a better idea, you can change this. On the Calculations tab, Cell D20 contains the number of days to go back. If you change this number, the calculation will still work, but you will need to recalculate every row again.

If you’ve no interest in diabetes, please skip this post. I’m aiming it squarely at anybody who measures their blood sugar.

These last few weeks, I have been working on a Diabetes spreadsheet. It is aimed at storing the values, when you use a glucometer to prick your finger. Well, okay, I have had a Diabetes spreadsheet for years, but just using UK units. After all, that was all I needed. This last few weeks, I made it international. That only took about a day, the rest of the time I have just been testing it.

The resulting spreadsheet, I share with you today. It is a spreadsheet containins macros (tiny snippets of programming) so I had to save it as a .XLSM file (m = macro), instead of the usual .XLSX extension. Furthermore, WordPress will not allow me to upload a .XLSM file directly, so I have had to place it inside an archive .ZIP file. To run the macros in the spreadsheet, you will have to allow Excel itself to run macros – there are instructions for this on the web, or contact me privately, and I will try to help.

What’s in the box?

When you open the spreadsheet, you will see two coloured backgrounds, white and grey. White are the cells in which you are expected to enter data, grey are the cells which are calculated for you. The cells you are expected to enter are just the date, time and the value of your reading. I’ve added ten dummy rows to give you an example to start with.

The very first cell is a dropdown to allow you to specify what units you want to use. There are two units used worldwide (mmol/l and mg/dl), and I support them both, but only one at a time. Whichever unit you choose, this is referred to as your primary unit. The other unit, the spreadsheet refers to as the secondary unit. I realise that the secondary unit might not be of interest to you, but I have written it in so that the spreadsheet can use either unit.

So, the values you enter. Column A is the date, column B is the time. I have formatted these columns to how I like them, but you can change this formatting if you wish.

Column C is used by the spreadsheet to store the datetime, which it will calculate and will use in its own calculations. Again, you can change the format if you wish.

Column D is the value of the measurement, in whatever unit you have chosen. Again, change the format if you wish.

At this point, please note that the spreadsheet expects the values to be added sequentially. In other words, if you took a reading on 1st January, 2021, it goes before the reading you took on 1st February, 2021, which in turn goes before the 1oth February, 2021 reading. And so on. The reason for this will become clear in a moment.

Now, here is why the spreadsheet is useful, beyond just recording the readings. What I find is that the number in Column D can be all over the place. Literally, it can be very low in the morning and very high in the evening, if I’m not careful. Because, they are spot values. So, on their own, the numbers don’t really give a clear idea of how well I’m controlling my sugar. Here’s an example:

See what I mean? It can be difficult to see what is going on. I find that it helps to apply statistics to the numbers, to talk about my average sugar rather than individual values. And the spreadsheet calculates these average values for you, that’s why it has macros in it. So, the chart above becomes:

which I think is clearer. In fact, the spreadsheet calculates your average sugar over the last fifty days. It also calculates a value called the standard deviation, which gives an idea how much your sugar varies each time. Note that the standard deviation calculation needs at least two readings in order to be calculated, as you will see if you look in the very first row of example data.

So when you ask it to calculate what these statistics were on a certain date, the thing the spreadsheet does is to find all the readings which are within 50 days, so as to include them in its calculation. That’s why the data has to be entered sequentially.

Therefore, Column E is the calculation, if I am going back up to 50 days, what is the earliest cell I count? Similarly, Column F tells you the total number of rows to be counted. In fact, as long as they are within 50 days, it doesn’t matter whether there are 1 or 1000 results. Columns E and F will be calculated for you.

Columns F and H contain the two interesting, statistical values, the Average and Standard Deviation, in your chosen unit. Again, I have chosen a format which suits me, you can change it if you wish. I also added a couple of graphs to my spreadsheet (5 minutes) but I shall leave that to you to do, if you wish. The graphs are pretty, but the numbers are more important.

Now, you remember we talked about the primary and secondary units? We have all the values in your primary unit, but Columns I, J and K show them in the secondary unit. Again, these fields are calculated and again, you may alter the format, or even hide, these columns, if you wish.

My last column, Column L, is just a free-text field I use to store the name of my monitor. This is just because, over the years, I have had several. Some will last years, others days. Build quality is usually not very impressive. Storing the monitor against its values might give me a clue if my values were consistently high or low, for example. That has never happened yet, but if it does… This column is not used in any calculations, you can delete it if you wish.

How to make a calculation

When you enter a date and a time, the datetime (Column C) is calculated automatically. Thereafter, though, the spreadsheet only performs a calculation when you hit the Calculate Statistics button at the top of the spreassheet. This is entirely deliberate, to allow you to type in values in peace, without the screen updating all around you.

When you hit Calculate Statistics, the first thing you see is a small dialog box which asks you where you want to start, and where you want to finish. This fits in with how I work. Every week or so, I walk the monitor over to the computer and type in each number. At the end, I only want to calculate the numbers for the last week, not every number. So I only need to calculate a dozen or so rows statistics each time. And, as long as you are just adding numbers, none of the previous numbers will change.

If you’re inserting numbers into the middle of the list, though, I recommend recalculating everything again. But the spreadsheet can do this, just so long as those dates and times are in the correct order.

When the macro has finished, it will pop up a little dialog box just to say as much. I have found on my machine that a calculation for a thousand rows might take 20s or so, but it will tell you when it is finished. Your computer might be faster or slower.

Lastly, the observant people among you will have notices that the spreadsheet has two tabs. Everything I talked about today refers to the Data tab. I’ll briefly talk about the Calculations tab in a day or two [update: this post is now written and may be found here]. Feedback, of course, is welcome.

https://mrbumpblog.files.wordpress.com/2020/04/sugar-us.zip

I hit my reader yesterday, and it suggested a post I might be interested in. It was from a diabetic guy, talking about his diabetes. The first thing he said was I am Type 1, so I can only really speak for myself. I can’t really speak for Type II diabetes. It’s funny because on my journey, one of my first questions was what’s the difference? I don’t know the full story but here is what I do know:

Diabetes (any type) is high blood sugar. Beyond that, there are several forms of diabetes. Type II is by far the most common, covering 80-90% of cases. Then comes Type I, covering about 10% of cases. There are other forms of diabetes too, but they are rare in comparison to the two biggies. These different types cover not the raised blood sugar itself, but the causes. Once your sugar is raised, you can look forward to the same lovely benefits, whatever type you have.

Type I is where your body attacks itself – it’s an auto-immune thing, resulting in no insulin being produced. Insulin counteracts glucose (sugar). If T1 is detected, it is often detected early, although in some people it can come on as an adult. The tratment is clear – insulin. There is empirical evidence to suggest a familial link, but the causes ultimately are not yet known, although links with lifestyle or weight have been ruled out.

Type II is where you don’t produce enough insulin, or where the insulin you do produce doesn’t work. It usually gets detected when you’re a bit older. In my case, I think of things degrading over time. There are various treatments which do various jobs – reducing sugar directly, or poking various organs to work harder. Ultimately, there is the same treatment as T1, insulin. T2 is often associated with lifestyle, although in my case the familial link is very strong, though the lifestyle link pretty much non-existent.

There is no cure for either T1 or T2, although with T2 things like what we eat and how much we exercise play a part, so some people can go into remission by changing their lifestyle. I didn’t, so this rule does not apply to everybody. I guess one of the reasons that there are more options with T2 than with T1 is research – I suspect that T1’s cause being as yet unknown is largely down to funding.

Either way, your body has an intolerance to carbohydrates. Many T1s will religiously carb-count, for the simple reason that it guides their insulin dose. I don’t carb-count, although possibly I should. My dose instead is guided by the less specific what did I eat (good/bad)?, or how much did I eat (big/small)? If I ate a lot of carbs, say, I take a 10% higher dose, but again this is empirical, not scientific.

Note that I am using the word carb above. Not sugar. Although sugar is a carb, so there is a link. But a bowl of pasta will do as much damage as a packet of candy.

The insulin that I take is pre-mixed. A fast acting insulin plus a slower-acting insulin. I did post about this ages ago here. The pen is marked just in “units”. Exactly what a unit is, I can look up, but the point here is that working out how many millilitres of a particular insulin I am getting requires a couple of calculations, plus the proportions of the two types of insulin are fixed at 3:1, so for every 3 of slower insulin, I am getting 1 of fast insulin. For these reasons, mixes tend not to be prescribed to T1 diabetics. (That one comes from my wife, who is a diabetic nurse.) The guy whose blog I was reading yesterday did, however, take the two types of insulin – they were different brands to what I take, but the same kind of thing – but took them separately, so he could vary the proportions.

As I said at the top of the post, I don’t consider myself an expert here, but this is just knowledge I have picked up along the way. In many ways it is immaterial, because regardless of the cause, we’re faced with the same problems. I chuckle, too, when I hear new stroke survivors talk about whether their stroke was due to a bleed, or due to a clot. Because that’s the language they hear from the staff. Whatever the cause, we’re all still in the same boat. But maybe that’s just me?

I’ve had better days. In fact so far today, everything that could go wrong, I think it probably has.

It started innocuously enough. One of the daily tasks in my getting up routine is to test my sugar. I posted about diabetes a little the other day. I mentioned the word test, but beyond that, I didn’t elaborate. I shall do so a bit here, and also tell you why it went wrong this morning.

There are various ways of testing your sugar. The most lo-tech is just peeing on a strip. The strip goes different colours depending on your sugar. As you can imagine, because you pee on it, it is totally painless. It’s also not very accurate. So most diabetics don’t rely on it. I don’t even have the strips.

By contrast, the most hi-tech is a full-on blood test. You go to your doctors surgery, there is a needle and syringe, they take some blood, send the sample off to the lab, and the next day you get your results. We’ve probably all had this test as part of a standard blood test, but if the value is in the expected range, generally nobody says anything.

There is a middle way, one favoured by most diabetics. It’s in the privacy of our own bathrooms, so there’s a big advantage straight away. It’s still a blood test, but only a drop of blood is required. Just like the image. You push the drop of blood onto a (nother type of) strip, and this goes into a machine called a glucometer. These things are tiny, easily fit into the palm of your hand, run off batteries, nice and portable. In about 5 seconds, the glucometer will tell you how much sugar is in your blood at that time.

I don’t know how they work, but it has got to be something electrical, because the machine is basically a circuitboard with a screen. At a guess, the drop of blood makes a circuit, they measure the voltage across the blood, and I guess that thanges with your sugar level. So they probably do a little conversion, and the number is on the screen.

Did you note back there I briefly mentioned pricking your finger? That in itself has become an art-form. I mean, you could start with a knife and cut yourself, but that’s way more blood than required, and way more pain!

So these machines have evolved where you pull a kind-of trigger, and they thrust a piece of sharp metal about 1mm into your skin! You know, shallow enough that you only bleed a drop of blood. But at the same time, that’s all the machine requires.

Now, these little pieces of sharp metal are called lancets. They come out of the factory sterile, and that’s just how they are intended to be used – a new lancet every time. But most diabetics don’t bother. I am the only person who uses my lancing machine, so I’m not particularly worried about catching something (new 🙂) from the last person who used it! So, the measure of when to change one of these lancets besically becomes when it gets blunt. Normally every month or so.

So, as you can imagine, all sorts of various finger-pricking contraptions have come along. They’re only really a bit of plastic, so every glocometer comes with one – the glucometer is the smart device here. It’s a bit like the wild west – every manufacturer, and pretty much every model, does its own thing.

And that’s what started the ball rolling this morning. I decided that the lancet was a bit blunt, so hunted out my stash of new lancets. It didn’t fit. Or rather, it did fit, but the bit of metal was too short to come and poke me. Even on maximum. You’re using the wrong lancet with that device, my wife says. I’m pretty sure I’m not, but I check anyway. So I raided a brand new glucometer. Fresh finger-pricker, fresh lancet. Same result. Thirty minutes of faff, and at that point the hammer came out – well at least that gave me some closure, made the outcome a bit more final! So, a big Thank You to Onetouch, for their totally shitty finger-prickers.

Not to worry, I have a backup. I measure myself twice a day, it is important so I keep backups of backups of backups… Basically, I take a new glucometer every time it is offered, and with every glucometer comes a finger-pricker.

I go to the bathroom to find the backup – not there. Well, it’s not where it is meant to be. It might be somewhere, but there is so much crap in there… So, I’n searching on my hands and knees – I’m never too sure I can get back up from there! In the end, I find the backup – some dozy twonk has put it in another cupboard, safe and sound. And, that dozy twonk might well have been me!

So, not quite midday – we still have things to do today – and all I really feel like doing is going back to bed.