AVCO formula Guidance

[punit83]

Hello !

i have created a excel for the shares i buy. i want to use formula for AVCO (Average cost method) method.
I am attaching file for reference please if some one can help me with the formula.

Stocks.xlsb
	A	B	C	D	E	F	G	H	I
1	#	Date	Status	C.Note #	Scrip Name	Qty	T.Rate	T.Amount	C.O.G
2	01	10/09/21	Buy	9794	Sun Tv	10	349.90	3,499.00	3,499.00
3	02	17/09/21	Buy	11810	Sun Tv	10	335.40	3,354.00	6,853.00
4	03	22/09/21	Buy	13100	Sun Tv	20	325.00	6,500.00	13,353.00
5	04	23/09/21	Buy	13773	Escorts	10	153.00	1,530.00	1,530.00
6	05	29/09/21	Buy	15139	DLF	10	159.00	1,590.00	1,590.00
7	06	29/09/21	Sell	15139	Escorts	-10	157.00	-1,570.00	-1,530.00
8	07	29/09/21	Buy	15139	Tata Steel	10	466.00	4,660.00	4,660.00
9	08	30/09/21	Buy	15491	DLF	25	151.50	3,787.50	5,377.50
10	09	30/09/21	Buy	15491	Escorts	25	151.00	3,775.00	5,305.00
11	10	30/09/21	Sell	15491	Sun Tv	-30	338.50	-10,155.00	-10,014.75
12	11	30/09/21	Buy	15491	Tata Steel	10	457.75	4,577.50	9,237.50
13	12	30/09/21	Sell	15491	Tata Steel	-9	471.55	-4,243.95	-2,097.00
14		Total				81	213.63025	17,304.05	37,763.25
Transaction

Cell Formulas
Range	Formula
H2:H13	H2	=IFERROR([@[T.Rate]]*[@Qty],"-")
I2:I13	I2	=IF([@Status]="BUY", (SUMIFS([T.Amount],[Scrip Name],[@[Scrip Name]],[Status],"BUY",[Date],"<="&[@Date])), (SUMIFS([T.Amount],[Scrip Name],[@[Scrip Name]],[Status],"BUY",[Date],"<"&[@Date])/SUMIFS([Qty],[Scrip Name],[@[Scrip Name]],[Status],"BUY",[Date],"<="&[@Date]))*[@Qty])
A2:A13	A2	=ROW(A1)
F14	F14	=SUBTOTAL(109,[Qty])
G14	G14	=IFERROR(Transaction[[#Totals],[T.Amount]]/Transaction[[#Totals],[Qty]],"-")
H14	H14	=SUBTOTAL(109,[T.Amount])
I14	I14	=SUBTOTAL(109,[C.O.G])

Thanks in Advance.

 

[alansidman]

If this is the result you are looking for, then below is the Power Query Mcode. If you are looking for something else, then provide us with a mocked up solution.

Book4 J K L 1 Scrip Name Status Average 2 Sun Tv Buy 4451 3 Escorts Buy 2652.5 4 DLF Buy 2688.75 5 Escorts Sell -1570 6 Tata Steel Buy 4618.75 7 Sun Tv Sell -10155 8 Tata Steel Sell -4243.95 Sheet1
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"T.Rate", type number}, {"Total", type number}, {"Qty", Int64.Type}, {"Scrip Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Scrip Name", "Status"}, {{"Average", each List.Average([Total]), type nullable number}}) in #"Grouped Rows"		​

 

[punit83]

If this is the result you are looking for, then below is the Power Query Mcode. If you are looking for something else, then provide us with a mocked up solution.

Book4 J K L 1 Scrip Name Status Average 2 Sun Tv Buy 4451 3 Escorts Buy 2652.5 4 DLF Buy 2688.75 5 Escorts Sell -1570 6 Tata Steel Buy 4618.75 7 Sun Tv Sell -10155 8 Tata Steel Sell -4243.95 Sheet1
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"T.Rate", type number}, {"Total", type number}, {"Qty", Int64.Type}, {"Scrip Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Scrip Name", "Status"}, {{"Average", each List.Average([Total]), type nullable number}}) in #"Grouped Rows"		​

Thank you sir for reply.
i am newbie to power query.

i will try to elaborate my requirement.

For Example below is purchase of sun tv with total avg cost of 333.825/Unit x 40 Total Units = 13,353.00 Total Amount

#	Date	Status	C.Note #	Scrip Name	Qty	T.Rate	T.Amount	C.O.G
01	10/09/21	Buy	9794	Sun Tv	10	349.90	3,499.00​	3,499.00
02	17/09/21	Buy	11810	Sun Tv	10	335.40	3,354.00​	6,853.00
03	22/09/21	Buy	13100	Sun Tv	20	325.00	6,500.00​	13,353.00
	Total				40	333.825​	13,353.00​	​

& below is my sold of sun tv

#	Date	Status	C.Note #	Scrip Name	Qty	T.Rate	T.Amount	C.O.G
10	30/09/21	Sell	15491	Sun Tv	-30	338.50	-10,155.00​	-10,014.75
	Total				-30	338.5​	-10,155.00​	-10,014.75​

i want formula which calculates the average cost of the goods on sell date calculated from before the sales.

in above example i can get cost easily because the sales date is after all the purchase date but i am not getting answer for below table where purchase & sales are in random dates

#	Date	Status	C.Note #	Scrip Name	Qty	T.Rate	T.Amount	C.O.G
01	11/01/21	Buy	70452.1	Quick Heal	30	394.90	11,847.00​	11,847.00
02	17/01/21	Buy	72217	Quick Heal	50	374.86	18,743.00​	30,590.00
03	01/04/21	Sell	12379	Quick Heal	-23	275.00	-6,325.00​	-8,794.63
04	04/05/21	Buy	26456	Quick Heal	10	182.00	1,820.00​	32,410.00
05	24/05/21	Sell	46610	Quick Heal	-10	240.50	-2,405.00​	-3,601.11
06	10/08/21	Buy	138135	Quick Heal	10	229.00	2,290.00​	34,700.00
	Total	6			67	387.612​	25,970.00​	97,151.26​