stock valuation help

I'm trying to value my stock based on current stock levels and previous purchase prices.

Simple example attached. It can be done by a weighted average but it's very manual.

I.e have to work out which purhcased stock to include in the weighted average based on current stock levels.

In reality I have 1,000's of products to cost, so could do with some fancy formula to make this easier to apply to mass products.

Thanks