Building models III: The build

In part I of this blog series I tried to explain the SIR model and in part II I started to use the published data on covid-19 to get some sensible numbers (or starting parameters) for my model. I now need to put the spreadsheet together.

Before you start

As I mentioned in previous posts, this spreadsheet exercise is to model the progression of, and mortality from, the imaginary virus Divoc-91. I’m using the data from the current Covid-19 pandemic, but the modelling is too simplistic, and starting parameters too uncertain, to make any of it’s predictions fictitious. It may however show general trends, which (I hope) might be interesting to some people, as they consider lockdown and social distancing measures.

Format of the spreadsheet

Each of the parameters below applies to this Excel spreadsheet (called SIR disease modelv2.xlsx) which is a downloadable file. Where a number appears in bold, red font the values is designed to be changed by you. The bold boxes contain what the value represents together with its value.

Cell B4 and M6 have conditional formatting: if they appear with a red background, stop and rethink your parameters.

The explanation for all these values should be in post II. But please comment if I’ve missed something out, or got one of the equations wrong!

Initial parameters

Cells A&B2: this is the number of days in each time unit.

Cells A&B3: the recovery rate (the b value in this SIR model is 0.05 per day) so this equation calculates the recovery rate per time unit.

Cells A&B4: This makes sure the model has enough time to run. With low Ro values, the disease spreads slowly and so it’s easy to ‘run out of time’. With low Ro values the time unit value may have to be set to 20 or 30 (so that the model runs for a total of 8 or 10 years).

Cells D&E2: the total population (the number of people in the model)

Cells D&E3: the number of infected people at the start of the model

Cells D&E4: the number of recovered people at the start of the model

Cells G&H2: the fraction of infections detected and confirmed as covid (since not everyone is tested).

Cells G&H3: the fraction of infections which end up in hospital (currently this is will be a similar value to the number of detected infections).

Cells G&H4: the fraction of infections which end up in ICU.

Cell G&H5: the fraction of those in ICU that die.

Cells J&K2: the number of hospital places in the population for Covid patients. (I’ve set that at 80% of the per-Covid estimates of 140000.)

Cells J&K3: the number of ICU places in the population for Covid patients. The UK government has a target of 30000 ICU places. There were about 4000 ICU beds pre-Covid so I’ve set this value to 28000.

Cells J&K4: this is the number of hospital beds available per day assuming that on average a patient remains in hospital for 14 days. This value is calculated from the value in cell K2 and the time period (in cell B2)

Cell J&K5: this is the number of ICU available per day assuming that on average a patient remains in ICU for 10 days. This value is calculated from the value in cell K3 and the time period (in cell B2)

Modelling columns

The next part of the spreadsheet (from rows 10 down to 110) contain the modelling equations and values.

Column A: contains the number of days from the start of the model. The day number is that day at the start of the period of time.

Column B: contains the Ro value for the time unit concerned. This allow you to set different Ro values at different times. Perhaps you would want to start with an Ro of 3.5 to start with and then go to an Ro of 1.2 after 6 weeks. Note that Excel columns can be autofilled with numbered: type the number in the first cell, highlight the cell, move the cursor to the lower right hand corner (so you get the cross) and double click: the rest of the column will fill with the number selected.

Columns C, D and E: contain the numbers of susceptible, infected and recovery people. The first values in the first row are taken from cells E2, 3 and 4. Subsequent rows are calculated from the previous rows and columns F, G and H (see below).

Columns F, G and H: contains the difference in the numbers of people in the susceptible, infected and recovery groups according to the SIR equations (see the blog post here) and the values in columns C, D and E.

Columns I, J and K: contains the number of new cases detected, the number of new cases in hospital and the number of new cases in ICU. These are calculated by using the value in column F (which is the number of people who catch the disease and so leave the susceptible group) and the values in cells H2, 3 and 4.

Column L: this contains a comment as to whether the capacity of ICU (which is calculated in cell K4) is exceeded. This was added after a discussion with a friend on facebook.

Column M: this calculates the mortality number in ICU from the values in column K and the ICU mortality rate in H5.

Column N: the column only shows a value if ICU has reached capacity. The value is the number of people requiring ICU minus the ICU capacity.

Column O: this is the mortality value within that time period from the deaths in ICU and the deaths of those who could not get into ICU because it was at capacity.

Column P: this is calculation of the infection doubling value (the amount of time it takes to double the number of infections). It only gives a value when infections go up.

The plots

There are two plots on the right hand side of the spreadsheet.

The top one shows the number of patients needing hospitalisations, needing ICU treatment and the mortality numbers, as well as the number of hospital and ICU places. (Note the log scale on the left hand axis.)

The next shows the number of people in the S, I or R groups. (Note the S and R values are on the left hand axis, while the I values – which are always smaller – are on the left hand axis.)

The mortality values

The mortality values for death in ICU, outside ICU (where I assume that everyone who can’t get an ICU bed dies) and the total number of deaths are given in M&N 2, 3 and 4. This misses out the deaths that happen in the community without hospital admission: for example, the BBC reported about 7% of covid deaths happening outside hospitals, whereas in Scotland the figure is more like 38%.


In the next blogpost, I’ll start typing in numbers and analysing the results.




Categories: Tags: , ,


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s