Weighted Variance in Query

Posted on 2006-04-18
Last Modified: 2010-08-05
Does anyone know how to calculate a weighted variance in Access.  I have a database with let's say 100 or so records that make up a mutual fund portfolio.  Each record includes a company, a weight in the portfolio and two attributes...let's call them size and style.  I would like to calculate a weighted variance for the 100 record table for both size and style with weights being the weights in the portfolio.  Thank you in advance
Question by:michaeljervis
    LVL 44

    Expert Comment

    can you show us a sample of the data that you are refering to?


    Author Comment

    Security Name      PERCENT WEIGHT      Style Score      Cap Score
    GOOGLE INC      6.90%      485.87      343.14
    MICROSOFT CORP      6.11%      184.47      395.85
    AMERICAN INTERNATIONAL GROUP      4.16%      100.00      366.82
    INFOSYS TECHNOLOGI      3.75%      347.65      338.97
    CARPENTER TECHNOLOGY CORP      3.72%      165.83      117.80
    SCHEIN HENRY INC      2.99%      176.93      150.30
    UNITEDHEALTH GROUP INC      2.86%      310.97      319.27
    COLGATE-PALMOLIVE CO      2.61%      177.27      264.22
    JACOBS ENGINEERING GROUP INC      2.59%      215.18      161.53
    QUALCOMM INC      2.51%      420.32      324.87
    AVON PRODUCTS      2.35%      202.52      221.16
    AMERICAN EXPRESS      2.24%      195.63      310.47
    MEMC ELECTRONIC MATRIALS INC      2.13%      244.38      189.21
    HALLIBURTON CO      2.07%      317.97      278.29
    AON CORP      2.03%      97.33      217.98
    TARGET CORP      1.92%      218.84      289.46
    PRUDENTIAL FINANCIAL INC      1.86%      139.41      278.81
    SEAGATE TECHNOLOGY      1.83%      152.66      215.27
    FIRST DATA CORP      1.82%      213.27      275.40
    NAVTEQ CORP      1.81%      344.42      156.85
    WALGREEN CO      1.77%      290.82      287.07
    SOTHEBY'S HOLDINGS  -CL A      1.74%      232.63      97.38
    VERISIGN INC      1.64%      233.34      170.65
    AGILENT TECHNOLOGIES INC      1.64%      256.31      229.17
    MONSTER WORLDWIDE INC      1.62%      295.61      173.95
    DANAHER CORP      1.62%      282.32      239.92
    VALERO ENERGY CORP      1.62%      56.52      277.32
    3M CO      1.61%      152.30      302.74
    RYANAIR HOLDINGS PLC ADR      1.55%      238.88      182.38
    MAXIM INTEGRATED PRODUCTS      1.50%      309.11      211.45
    AMERITRADE HOLDING CORP      1.49%      228.21      191.76
    MONSANTO CO      1.48%      279.76      249.28
    PAYCHEX INC      1.48%      351.96      227.98
    WELLS FARGO & CO      1.47%      89.81      339.38
    BEST BUY CO INC      1.36%      290.21      259.97
    RESMED INC      1.32%      301.81      133.57
    VOLCOM INC      1.20%      329.02      58.41
    EOG RESOURCES INC      1.18%      295.91      233.62
    EBAY INC      1.12%      455.68      300.30
    CHESAPEAKE ENERGY CORP      1.09%      161.24      210.05
    SIRF TECHNOLOGY HLDGS INC      1.04%      299.45      100.55
    AMERICAN TOWER CORP      1.01%      452.80      214.32
    ST JUDE MEDICAL INC      1.00%      350.91      225.21
    UNIVERSAL TECHNICAL INST      1.00%      186.79      57.19
    PEABODY ENERGY CORP      0.98%      416.42      217.77
    CAPITALSOURCE INC      0.93%      203.26      140.02
    TEAM INC      0.93%      244.90      -7.30
    Q-CELLS AG      0.92%      457.59      60.89
    WESCO INTL INC      0.88%      200.80      135.72
    ROCKWELL COLLINS INC      0.83%      228.27      199.58
    NICE-SYSTEMS LTD. ADR      0.82%      169.53      173.69
    COLDWATER CREEK INC      0.77%      341.42      121.87
    ALCON INC      0.61%      390.03      271.10
    UNOVA INC      0.52%      278.41      104.97
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hi michaeljervis,

    SELECT SUM([PercentWeight] * [StyleScore]) AS WtdStyle, SUM([PercentWeight] * [CapScore]) AS WtdCap
    FROM YourTable

    For weighted averages:

    SELECT SUM([PercentWeight] * [StyleScore]) / COUNT(*) AS WtdAvgStyle, SUM([PercentWeight] * [CapScore]) / COUNT(*) AS WtdAvgCap
    FROM YourTable



    Author Comment

    Can you please help me understand what the formula is doing.  I am not very literate in database speak.  Is this calculating variance, the statistical function?  I know that variance is often refered to us the mere difference in values but the statistical variance I am looking for is :

    Definition: The variance of a distribution is the average of squares of the distances from the values drawn from the mean of the distribution:
    LVL 44

    Expert Comment

    No, the SQL that matthewspatrick shows is NOT calculating the variance.  In fact, I doubt that you can calculate varinace in SQL.  You will need to get the data into a recordset, and then do the calcualtions entirely in your program code, not by executing a simple SQL statement.

    LVL 92

    Expert Comment

    by:Patrick Matthews
    My SQL is *not* getting a variance--I misread the question.  As AW notes, you cannot get a variance
    with SQL alone--you would need to use VBA to get there.

    LVL 58

    Expert Comment


    Statistical variance can be expressed as the mean of squares minus the square of the mean. This can easily be calculated in SQL.

    We will use (weighted statistics):

        N = Sum( weight )
        Mean of "size" [M] = Sum( weight * size ) / N
        Mean of squares [S] = Sum( size^2 * weight ) / N
        Square of mean = M^2
        Variance (population) = S - M^2

    In this case, N=1 (percent weights), which simplifies things. In SQL:

    SELECT Sum(size^2*weight) - Sum(weight*size)^2 As VarP FROM ...


    Author Comment

    My apologies, but I neglected to mention that I have many portfolios and many dates to peform this analysis on.  I have unique date (date) and fund (fund) fields that I would like to have variance statistics performed on.  Is there a way in the code above to group on date and fund?

    While I use access quite a bit, I am a real novice in SQL and VBA.  Do I just create a new query and paste your code into the query somehow.  Thank you for your guidance and patience.
    LVL 58

    Expert Comment


    The expression for the variance is the same regardless of grouping, much like the meaning of Sum(), Count() or Avg() are the same. The only check you should make is that Sum(weight) = 1 in each group. If you can't or don't want to do that, use the full formula for weighted variance:

        VarP: Sum(value^2*weight)/Sum(weight) - (Sum(value*weight)/Sum(weight))^2

    As for a grouping example, let's add a check, but use the full expressions:

    SELECT [date], fund,
        Sum([PERCENT WEIGHT]) As TotalWeight,
        Sum([Style Score]^2*[PERCENT WEIGHT]) / Sum([PERCENT WEIGHT])
         - (Sum([Style Score]*[PERCENT WEIGHT]) / Sum([PERCENT WEIGHT]))^2 As VarStyle,
        Sum([Cap Score]^2*[PERCENT WEIGHT]) / Sum([PERCENT WEIGHT])
         - (Sum([Cap Score]*[PERCENT WEIGHT]) / Sum([PERCENT WEIGHT]))^2 As VarCap,
        Count(*) As NbEquities
    FROM tblYourTable
    GROUP BY [date], fund

    Again, if you are independently certain that TotalWeight is always 1 in each group, you can remove all the Sum([PERCENT WEIGHT]) divisors. However, I would leave in the check, i.e. the column TotalWeight, just to make sure.

    On the other hand, you can use any other value for weight: total market value, number of shares, etc., or basically the figures from which the percent weights are derived from.

    Good luck!
    LVL 58

    Accepted Solution

    Forgot this question:
    > Do I just create a new query and paste your code into the query somehow?

    Basically, yes. You can create a blank new query, not add any table, switch to SQL view and paste the query. Then make sure to check every single name: table name and field names.

    You can also create a new query based on your table, display the totals row (menu "view / totals"), and add new expressions in the columns of the query grid. Make sure that the "total row" shows "expression" for those I gave you here. For example:

        TotalWeight: Sum([PERCENT WEIGHT])     (Total row: Expression)
        TotalWeight: [PERCENT WEIGHT]       (Total row: Sum) -- identical


    LVL 58

    Expert Comment

    Glad this helped. Will we be seing questions about volatility and Sharpe ratio next? ;)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now