I am trying to do a weighted multiple least squares regression on utility data. Basically I have utility bills where I use : Days billed, Consumption. For the same billing period I calculate Heating Degree Days and Cooling Degree Days.

$ HDD = (18-Tout)^{+} $ and $ CDD = (Tout - 21)^{+}$

Where the + sign here means it equals zero if negative. Tout = Outside Temperature, given in Celsius.

So, I used to do it like this : $$ Y = X\beta $$

Where : Y = Column vector, size (12,1), with each "monthly" consumption

X = Matrix of size (12,3). Column 1 is the number of Days in the billing period (i). Column 2 is HDD(i) and Column 3 is CDD(i)

$\beta $= The coefficients I'm looking for. Size (3,1)

$$ \beta = (X^{T}X)^{-1}X^{T}Y $$

That works almost perfectly in most cases. But the fact is that each billing period is NOT equal in length, even if billed monthly (take February...). And in most cases the reading date varies. So that method will give me a non-zero Net Bias Error due to data length variation.

That's where I'm struggling. I tried doing the following:

  • redefining X as having the first column being full of 1.
  • Defining a matrix W that is diagonal. Each $ Wii = Days(i)/DaysTot $

And solving for

$$ \beta = (X^{T}WX)^{-1}X^{T}WY $$

But I cannot seem to make it work. What am I missing? Can you point me to the right direction please?

Sample Data below, each line corresponding to one billing period (almost a month, but not exactly and that's the problem). You have Y (the kWh reading), the days in that billing period, and the corresponding sum of daily HDD and CDD.

Y      Days  HDD    CDD
30,343  31  0.00    0.00
25,193  31  0.00    29.67
24,719  30  0.00    70.67
66,993  31  41.61   0.00
202,271 30  166.61  0.00
297,333 31  243.44  0.00
393,891 28  341.33  0.00
505,941 31  433.83  0.00
485,715 31  431.06  0.00
412,160 30  350.67  0.00
346,495 30  298.44  0.00
138,180 36  86.22   0.00

Here is a spreasheet where I explain the problem more, show one example of what I'm trying to do in a case where there is only heating involved, and then one case where there is heating and cooling. For heating and cooling, I show what I've been doing so far, and then three attempts at correcting my methodology. The last attempt could be the solution but I am not sure at all.

Download the Excel file here (.xlsx, 56ko): http://www.filedropper.com/weightedregressionforenergy

ASHRAE Guideline 14 describes the problem and its solution for a case where only heating is involved (one variable): http://gaia.lbl.gov/people/ryin/public/Ashrae_guideline14-2002_Measurement%20of%20Energy%20and%20Demand%20Saving%20.pdf Read pages 139 (141 in the PDF) and the couple next ones. I am just trying to find a solution for a case where heating AND cooling are involved (two or more variables)

  • $\begingroup$ It seems that you are doing multiple (and not "multivariate") regression. Also, that you have many time series. Did you treat them as panel data, or you run estimation on each time series separately? $\endgroup$ – Alecos Papadopoulos Dec 11 '13 at 17:15
  • $\begingroup$ Thanks for your interest. I just renamed the columns in the data sample. Each row is one billing period (roughly one month). Then Column 1 is the consumption (vector Y in my definition), and the three other colums are basically the matrix (12,3) called X in my definition. I wasn't sure whether I should call it multivariate or multiple since columns of matrix X are somewhat correlated. Does that clarify it a bit? $\endgroup$ – Julien Marrec Dec 11 '13 at 21:24
  • $\begingroup$ Multivariate or multiple regression is a matter of how many columns there are in Y. You need two or more columns in Y to call it multivariate. Having several columns in X is neither necessary nor sufficient for it to be multivariate regression. (The term "multiple" may slowly fade away because multiple X is hardly a big deal any more.) $\endgroup$ – Nick Cox Dec 12 '13 at 1:20
  • $\begingroup$ Ok thanks for this correction. I edited my post to not say it's multivariate. Anyhoo, can someone help please? $\endgroup$ – Julien Marrec Dec 12 '13 at 14:32
  • 1
    $\begingroup$ (1) the matrix notation is cut-and-past from textbook definition of regression. It doesn't convey any information on you specific model (2) similarly there is no specific problem with your model (3) net bias error isn't standard regression terminology (4) unclear if you've done any standard regression diagnostics (5) you only have 12 observations ?! (6) you're using excel ?! $\endgroup$ – charles Dec 14 '13 at 22:19

Summary: there are various biases, errors and hidden terms that may or may not vary directly with number of days in a period. Simple compensation for length of period may fix some biases, but introduce others.

Try 4 in your spreadsheet is the one where you have coefficients for energy per CDD per day, and energy per HDD per day; and where you've also weighted each month's reading by the proportion of the year it represents. That looks correct to me. I have never knowingly used the ASHRAE calculation, and couldn't say whether or not what you've done is consistent with it, but your calculation in Try 4 does look to be the best of the four methods.

Detail: I can see a few ways in which your multiple regression could hit problems.

HDD and CDD already both include an impicit representation of the length of each period. You can adjust for this by dividing each value for energy use, HDD and CDD by the number of days in the relevant period, and then doing the regression, but there are a couple of possible hidden problems.

One of the two problems is hidden in what the energy readings include: it's not clear from your question whether they represent only consumption that is expected to be directly proportional to HDDs and CDDs, or whether they might also include other items, that are directly propoprtional to the length of the period (e.g. lights that are on constantly at constant levels), or that vary loosely per period (perhaps by number of weekdays, or weeekend-days, per period), or have some other relationship (e.g. lights that only come on when it's dark, and so follow a sinusoidal pattern over the year).

The other hidden problem is your HDD and CDD values may not be representative for the building. We don't know from your question how you arrived at that choice of set-points for heating and cooling, from which you've derived HDD and CDD. Set-point temperatures could vary by time of year, the heating or cooling systems may not always be sufficient to deliver the equilibrium set points assmed, and lag and thermal-mass effects will introduce biases and errors, any of which may or may not be proportional to period length, HDDs or CDDs.

| cite | improve this answer | |
  • $\begingroup$ Thanks. All of these are very good points, but this is not exactly what I'm trying to discuss here. I've been doing regressions for a while (but with a bias) and I'm pretty satisfied with the outcomes in most cases. There's a lot of litterature about weather normalization that discuss the type of models you can use but never how to actually apply it (ex: IPMVP, ASHRAE Guideline 14) The HDD and CDD here are also not to be discussed (I usually make them vary too, and check whether it improves the fit or not). Let's assume it's a building with lights, and a heater, and an A/C... $\endgroup$ – Julien Marrec Dec 14 '13 at 20:58
  • $\begingroup$ I have added a spreadsheet in my post that should hopefully make it clearer. Mind taking a look sir please? $\endgroup$ – Julien Marrec Dec 14 '13 at 20:59
  • $\begingroup$ I guess I'm looking at the annual total consumption between observed and predicted. Is "try 4" the equivalent of the method given by ASHRAE and shown on the right of the tab "Gaz.."? Is that the proper way to include weights? $\endgroup$ – Julien Marrec Dec 15 '13 at 10:54
  • $\begingroup$ Alright, thanks. I've validated your answer and gave you the bounty for your troubles! I guess I'll go with that. Now I need to figure out how to correctly calculate CV(RMSE) and such metrics correctly. In that case I have 3 parameters for the calculation of Rsquare adjusted right? HDD, CDD and Days $\endgroup$ – Julien Marrec Dec 15 '13 at 11:52
  • $\begingroup$ Oh and I'd be curious about what you do use since you seem familiar with utility bills analysis. $\endgroup$ – Julien Marrec Dec 15 '13 at 11:54

It seen the post haven't been update for a while. I suppose you have found the answer by the time, but i have been struggling with the exact same problem. I've been doing weighted linear regression as describe in ASHRAE Guideline 14 for single x variable. But never been able to make multiple weighted regression that have no net bias error.

Like you I have tried the matrix approach with octave and R .... β=(XTWX)−1XTWY ... which gave me net bias error even with single weighted regression. I have found this paragraph in page 139 of guideline 14:

"The solution is to weight each data point by the length of the time period from that point. Most standard statistical packages can perform weighted regressions. If only a spreadsheet program is available, the same result can be obtained by including each point as many times as the length of data covered. In this example, that would mean 33 points from observation number 1, 27 from observation number 2, etc."

So I tried making ordinary multiple least squares regression (using HDD and CDD ) by this method using excel Analysis ToolPak and it work great. I have no more net bias error. I think you need to recalculate the stats because of the wrong number of data ( 365 instead of 12)


| cite | improve this answer | |
  • $\begingroup$ This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. You can also add a bounty to draw more attention to this question once you have enough reputation. $\endgroup$ – gung - Reinstate Monica Oct 20 '15 at 16:25
  • $\begingroup$ I have reedit the answer since I have finally found the answer, it's true it was not really an answer. Next time I will add comment or ask Question $\endgroup$ – Phil Oct 21 '15 at 14:54
  • $\begingroup$ "eronic"? That's not an English word. What did you mean by it? $\endgroup$ – whuber Oct 21 '15 at 15:27
  • $\begingroup$ Sorry I meant ''the erroneous number of data''. Since the regression his made of 370 values ( sum of the number of days in the exemple) instead of the initial 12 values. I think the CV(RMSE) (for exemple) need to be calculate considering there is 12 value. $\endgroup$ – Phil Oct 21 '15 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.