Category: Uncategorized

I saw a spreadsheet with some VBA macros recently to calculate numbers of seats awarded in elections conducted using the d’Hondt system, so I thought I’d have a go at a single Excel function to do the same. WordPress doesn’t allow the Excel workbooks with VBA to be uploaded, so the file which can be downloaded from this link needs to have the following code added in a module

3rd June 2020 I had a comment from Daniel Martinez pointing out a bug. Hope I have fixed this now, and also addressed the problem of ties

d’Hondt workbook – with VBA stripped

Option Explicit

Public Function dHondt(iSeatsToAllocate As Integer, rVotes, Optional lVoters As Long = 10000)

Dim dVoteTotal As Double
Dim dVote
Dim i As Integer
Dim aSeats()

Dim dVotes() As Double
Dim blnColumn As Boolean

Dim dSeatCostTrial As Double
Dim nSeatsTrial As Integer

Dim nParties As Integer

Dim iTries As Integer
Dim dUpperLimit As Double, dLowerLimit As Double

Dim blnScaled As Boolean
Dim dChange As Double

    On Error GoTo eh
    
    dVotes = asSingleArray(rVotes, blnColumn)
    
    nParties = UBound(dVotes) + 1

'The algorithm looks for a cost per seat which will allocate the number of seats available
'starting with upper and lower limits, and the first trial cost per seat the upper limit

'One or other of these limits is set to the mid point of their range, according to whether the
'number of seats allocated at the trial cost per seat is above or below the number to allocate

'Where there are ties in the number of votes, the basic d'Hondt process will not be able to
'allocate seats, so some other tie breaking process will be needed. This condtion is tested for
'by seeing if the difference between upper and lower limits is less than a single vote. For this
'to work when percentages are given, a total number of votes is needed. This is an option third
'argument to the function. By default it is 10,000

'Lower limit
    For Each dVote In dVotes
        dVoteTotal = dVoteTotal + dVote
        
        If dVote <> Int(dVote) Then blnScaled = True
        
        If dLowerLimit = 0 Then
            If dVote > 0 Then dLowerLimit = dVote
        Else
            If dVote > 0 And dVote < dLowerLimit Then dLowerLimit = dVote
        End If
    Next
    
    dSeatCostTrial = dVoteTotal / iSeatsToAllocate ' - always high
    dUpperLimit = dSeatCostTrial
    
    dLowerLimit = dLowerLimit / iSeatsToAllocate
    
    While nSeatsTrial <> iSeatsToAllocate
    
        iTries = iTries + 1
        
        ReDim aSeats(nParties - 1)
        nSeatsTrial = 0
        
        For i = 0 To nParties - 1
            aSeats(i) = Int(dVotes(i) / dSeatCostTrial)
            nSeatsTrial = nSeatsTrial + aSeats(i)
        Next
        
        If nSeatsTrial > iSeatsToAllocate Then ' adjust dSeatCostTrial up
            If dSeatCostTrial > dLowerLimit Then dLowerLimit = dSeatCostTrial
        ElseIf nSeatsTrial < iSeatsToAllocate Then ' adjust dSeatCostTrial down
            If dSeatCostTrial < dUpperLimit Then dUpperLimit = dSeatCostTrial
        End If

        dSeatCostTrial = (dUpperLimit + dLowerLimit) / 2
        
        dChange = dUpperLimit - dLowerLimit
        If blnScaled Then dChange = dChange * lVoters
        
        If dChange < 1 Then
            Err.Raise 1, "dHondt", "Check for tied votes"
        Else
            dSeatCostTrial = (dUpperLimit + dLowerLimit) / 2
        End If
        
    Wend


tidyup:

    If blnColumn Then
        dHondt = WorksheetFunction.Transpose(aSeats)
    Else
        dHondt = aSeats
    End If
    
    Exit Function
    
eh:
    
    For i = 0 To nParties - 1
        aSeats(i) = Err.Description
    Next

    GoTo tidyup
    
    
End Function

Private Function asSingleArray(rVotes, blnColumn As Boolean)


Dim i As Integer
Dim aVotes
Dim dVotes() As Double


    Select Case TypeName(rVotes)
    
    Case "Range"
        aVotes = rVotes.Value
    Case "Array"
        aVotes = rVotes
    Case Else
        Err.Raise 1, "", ""
    End Select
    
    On Error Resume Next
    blnColumn = UBound(aVotes, 2) <= 1
    
    If Err.Number <> 0 Then  'single dim array
        ReDim dVotes(UBound(aVotes) - LBound(aVotes))
        For i = 0 To UBound(aVotes) - LBound(aVotes): dVotes(i) = aVotes(i + LBound(aVotes)): Next
    ElseIf blnColumn Then
        ReDim dVotes(UBound(aVotes) - LBound(aVotes))
        For i = 0 To UBound(aVotes) - LBound(aVotes): dVotes(i) = aVotes(i + LBound(aVotes), LBound(aVotes, 2)): Next
    Else
        ReDim dVotes(UBound(aVotes, 2) - LBound(aVotes, 2))
        For i = 0 To UBound(aVotes, 2) - LBound(aVotes, 2): dVotes(i) = aVotes(LBound(aVotes), i + LBound(aVotes, 2)): Next
    End If
    
    asSingleArray = dVotes
    
End Function

The function needs first the number of seats to allocate, and then an Excel row or column range with the votes cast, or percentages, so something like

={dhondt(Q$3,Q$4:Q$13)}

The output is another Excel range, a row or column as the case may be, with the number of seats awarded under d’Hondt. Those curly braces are the result of the function being entered as an array formula – something to look up for those unfamiliar with these.

I’ve also written it so that it can be used more simply in VBA with just a single dimension array as the second argument.

Copyright? I’m really not too bothered, and I’m sure plenty of other people have done something like this, but yes, acknowledgement is appreciated.

Tim Lund

23rd April, 2019

I was going to title this blog ‘house prices, supply, rents, incomes and interest rates‘, but then wondered if any would be readers would still be awake.

The background is the discussion which has been going on for a while about whether UK house prices so high because of a lack of supply, but it also leads me to some thoughts about the linguistics of ‘rent’

Continue reading A former bond analyst thinks again about house prices

“Elder Stubbs” is an allotment site in East Oxford owned by a charity rather than the local authority.  Something of its history can be found on its website here, but this skips the period in the 1990s when the charity found itself at loggerheads with Oxford City Council.  For this I am grateful for the privately printed notes on the history of Elder Stubbs, written by John Purves, formerly Chair of the Elder Stubbs Trustees. Continue reading Elder Stubbs and Existing Use Value

This is a final attempt to get TalkTalk to contact me, and provide my father with the broadband connection he is paying them for.

I struggle to find the words to describe how frustrating it is to talk to TalkTalk, but their customers seem to be locked into a Catch-22 – until the connection is working, you can only communicate by phone – they will not use email.  So, if I try to connect from my father’s home, I get no further than this:

Broadband service not enabled

Contact provider for activation date

(Note to the right the wireless router, with TalkTalk branding, with all lights working; there is nothing wrong with this router,or connections to it from the PC)

but if I try to contact via a customer service from my own PC, using t this URL, https://myaccountholding.talktalk.co.uk/, it seems I have to be using a home TalkTalk connection:

So for months now I have been on the phone to TalkTalk call centres, sometimes getting through quickly, sometimes hanging on for what seems like hours – probably no more than 20 minutes, but it feels like hours – and each time being asked the same questions, and giving the same answers. Yes, I have switched the router on and off, more times than I care to remember.

To begin at the beginning, in December last year my father moved house, to an address two doors away from his old address.  Before the move, TalkTalk had been providing both phone and broadband, and I assumed changing would be completely straightforward.  Eventually the phone service came through, but not the broadband.  I got on the phone, and after a lot of questions, I was assured that things had been sorted, and the broadband would resume at midnight; it did not.

So that he would at least be able to read emails, I approached a next door neighbour, who kindly shared the password to his wireless router, which could be detected through the wall, but explained that usage with his contact was limited.  I didn’t suppose that, for the few days it would take to sort things out, our usage would cause a problem.  So this other router was set up on my father’s PC as an alternative to connect to the internet.

It’s possible that all TalkTalk customers should keep a log of what phone calls they make, because here I can’t say for sure when I next spoke to them.  I would expect that TalkTalk have some system for logging calls from customers, but they do not seem to be able to share this.  Anyway, in due course, with the problem still not fixed, TalkTalk arranged to send an engineer to visit, explaining – over the phone – that the problem had either to be to do with the wiring of the router, or something to do with the wiring of my father’s new home.  I was a bit sceptical, since it was the same router as had worked fine in the old house, and the new house had recently been rewired.  But they gave a date and time slot when the engineer would arrive, and I made it down there in time, hoping to be able to ask the engineer what he found.  I rang TalkTalk to get a more exact estimate of when the engineer would be arriving – but they didn’t know.  Later my father came back from his shopping, and happily said – “so it’s all fixed?”.  Apparently the engineer had called him to ask if it was OK for him to come early, and my father had agreed, but not asked what he had done, or any problems he had found.  In fact, it seems he found no problem, so felt able to tell my father that everything was fine.

Well, when I switched the wireless connection back to his router, it wasn’t working, so I got on to TalkTalk, again, and again was told it was just a matter of waiting till midnight, and then the service would the there.  So, I sent him an email, and asked him to check his emails next morning.  Good news – he was able to read my email, and I thought everything was fixed.

I started to worry, however, when other family members visited, and reported being unable to connect to the internet via his router, and the suspicion crossed my mind that when he was checking emails now, or looking at website, his PC was connecting via the neighbour’s router, having it on a list of alternative routers to try if the first one failed.  So, when I visited again yesterday, I had a look via the Control Panel – and yes, that was what was happening.  So now I need to ask the kind neighbour if we have been running up unexpected bills for him – embarrassing.  Of course I got on to TalkTalk – by phone – and was asked all the same questions, and gave all the same answers.

In retrospect, we should probably have switched from TalkTalk long ago, but I don’t want to have an interruption to the phone service while moving to another supplier, and up to a point, it seems more reasonable to suppose any supplier – even TalkTalk – will be able to work out what might be causing a problem, and fix it.  I guess that’s why I’m writing this – in the hope that by putting something in the public domain, I can get TalkTalk to try to sort out the problem, rather than just leave it as some hassle which goes no further than a poor call centre underling somewhere in India.

I’m also curious to know how or why TalkTalk’s service can be so bad.  Somewhere within the organisation there must be the information which will identify the problem.  Do they simply economise on staff with sufficient training to deal with problems, and accept that customers who encounter problems will just shrug their shoulders, and move on to another supplier?  I guess that is the basic business model justifying – to shareholders – the provision a poor service to customers.

More recently it struck me that TalkTalk must also log the actual broadband usage of its customers, since otherwise they would not know how to charge them.  So they could identify how much broadband my father has used – which I suspect now is none.

[mapsmarker layer=”1″]