Finding a weighted average

[oldman]

I track my stock purchases using Access and would like to be able to find the average price I paid for a stock. If I buy 10 stocks on June 1 at $5 and turn around and buy 15 more on July 5 at $6.50, how can I find the average price paid. It is obviously not the $5.75 that the "average" function will return because I bought more at the higher price than at the lower price. In simple terms I want to calculate the result of ((10 x 5) + (15 x 6.50)) / 25.


This will give me the real average price that I paid for the 25 stocks.
I can write a query that totals the number of stocks of the transactions of XYZ stock and the total expenditures for those same stocks using a "sum" function but then reducing that to an average price per share should have an "elegant" solution, not multiple queries that finally arrive at an answer. A simple "sum" type query "grouped" by the ticker designation, in this case XYZ, will give me total shares or total costs so I can do this with 2 queries where the third query would link the first two results and give the result of "sum of cost" divided by "sum of quantity" but that seems like the long way around.
I really look forward to hearing from the experienced Access programmers here. I do enough routine Access manipulation of data at work to know such a thing must be possible but it is the type of question that never arises in my workplace. If we could still dump things to Excel in a read / write mode as we could before 2003, I would use an external XL file to do the calculation, it would be as easy as can be that way but I can no longer write to an external XL file, I can only create a new one with an export, thanks for the downgrade Microsoft.

[ajetrumpet]

yes it is possible. the only shortcoming to a need like this in Access is that it requires a boat load of thinking to write the function. but once you have that, the future is a breeze.

I love doing this kind of thing...especially inventory accounting in Access because it's difficult. IMO, you cannot get an accurate answer to this without showing your table where the stock transactions are kept. Every structure is different, and the smallest difference can mean big changes in the code writing. Even a sample table using the forum tags would be fine, as long as it shows the structure that you look at in Access. (fields and types)

[oldman]

My tables are absolutely as simple as I can make them. I really do understand Access at a gut level and know that complexity means problems in the long run. Here is the field structure that I work with.
Stock ID......... Number of shares..........total cost.........cost per share.......... There are other fields that really don't enter into my question like the cost of the broker transaction like the $7 per transaction that Scottrade advertises all the time.

[ajetrumpet]

My tables are absolutely as simple as I can make them. I really do understand Access at a gut level and know that complexity means problems in the long run. Here is the field structure that I work with.
Stock ID......... Number of shares..........total cost.........cost per share.......... There are other fields that really don't enter into my question like the cost of the broker transaction like the $7 per transaction that Scottrade advertises all the time.

what is the weight factor? number of shares I assume? (there really isn't anything else to consider).

say you have a table called "stockTrans" and the fields called "sharesNo" and "sharesCostPer". In this scenario, the weighted average Cost per share could be calculated in a query as:

SELECT SUM([sharesCostPer] * [sharesNo]) / SUM([sharesNo]

AS WeightedAvg

Alternatively, a weighted avg of transaction cost might be:

SELECT SUM([sharesCostPer] * [totalCost]) / SUM([totalCost]

AS WeightedAvg

I'm not sure about the accuracy of the last one because totals are not normally the factors in weighted avg's. But it does follow the same concept!