hedonic pricing in a nutshell

 

 Hello everyone!!

 

I was just mapping some areas and their home prices. Suddenly I got this idea to test Hedonic pricing technique and here I am with good news. Firstly, we can regress data for 150+ variables just at a click of a button. I’ve done some VBA coding to Auto populate testing data from a database sheet. Then, all the results were  configured e.g., Regression (=Linest()), Hedonic price through the Multi-linear Regression equation and forecast result (=Forecast.Linear()).

Hedonic pricing is a good measure when there is no data vailable to attain a fair value of the property from other properties, econometrics etc in an area. It is also used for economic analysis of the surroundings and in this model, I have used random variables and chosen only the ones which has a statistical significance, when R-Squared shows good correlations and thus, I was able to get a reasonable price prediction. This was again due to the quality of data used from OECD and National Statistics Centre’s.   

 

Here is the code:

 

Sub AutoPopulateData()

    Dim selectedName As String

    Dim wsSource As Worksheet

    Dim wsTarget As Worksheet

    Dim lastRow As Long

    Dim i As Long

   

    ' Set references to the worksheets

    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Change to your actual sheet name

    Set wsTarget = ThisWorkbook.Sheets("Sheet2") ' Change to your actual sheet name

   

    ' Get the selected name from Sheet2, cell G1

    selectedName = wsTarget.Range("G1").Value

   

    ' Find the last used row in Sheet1 (column B)

    lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row

   

    ' Loop through the rows in Sheet1

    For i = 2 To lastRow ' Start from row 2 to exclude headers

        If wsSource.Cells(i, "B").Value = selectedName Then

            ' Copy columns B to G to Sheet2

            wsSource.Cells(i, "B").Resize(1, 6).Copy wsTarget.Cells(wsTarget.Rows.Count, "B").End(xlUp).Offset(1, 0)

        End If

    Next i

End Sub

 

 

 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim selectedCustomer As String

    Dim salesDataRange As Range

    Dim salesData As Range

    Dim i As Long

   

    ' Define the range containing customer names (Sheet1 B1:DQ1)

    Set salesDataRange = Worksheets("Sheet1").Range("B1:DQ1")

   

    ' Check if the change occurred in any cell with a dropdown list

    If Not Intersect(Target, Me.Range("B1:DQ1")) Is Nothing Then

        ' Get the selected customer name

        selectedCustomer = Target.Value

       

        ' Find the corresponding sales data for the selected customer

        For i = 1 To salesDataRange.Columns.Count

            If salesDataRange.Cells(1, i).Value = selectedCustomer Then

                ' Set the sales data range (Sheet1 B2:DQ13)

                Set salesData = salesDataRange.Offset(1, i - 1).Resize(13)

                Exit For

            End If

        Next i

       

        ' Populate the sales data in the same row of Sheet2

        Target.Offset(1).Resize(13).Value = salesData.Value

    End If

End Sub

I have done only 5-variable test cause Linest Function supports only 11 variable test and Excel’s Data Analysis’s Regression tool can analyse data up to 16 variables.

However, the data can be populated into any range by adjusting the second code above.

 

Note: what is include in pricing a home is your wish or one can follow the government's real estate regulations. One can use this for all types of Regression testing.  

 

 

Bye