Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 975
  • Last Modified:

Weighted Variance in Query

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
  • 4
  • 3
  • 2
  • +1
1 Solution
can you show us a sample of the data that you are refering to?

michaeljervisAuthor Commented:
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
Patrick MatthewsCommented:
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


Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

michaeljervisAuthor Commented:
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:
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.

Patrick MatthewsCommented:
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.


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 ...

michaeljervisAuthor Commented:
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.

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!
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


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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now