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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glad this helped. Will we be seing questions about volatility and Sharpe ratio next? ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.