Here is the code with comments added, I hope it will help you follow and understand what each part does!
Code Display MoreSub Inventory() Dim x, y, Ticker, i As Long, ii As Long, iii As Long Dim iv As Long, v As Long, lBuy As Long, lSell As Long, d As Double ' Load all the data into array x x = Sheet1.Cells(1).CurrentRegion ' Scripting Dictionary is used to obtain a unique list of Tickers ' That list is loaded into an array named "Ticker" With CreateObject("scripting.dictionary") For i = 2 To UBound(x, 1) If Not .exists(x(i, 1)) Then .Add x(i, 1), Nothing Next Ticker = .keys End With ' Array y is resized according to the number of unique Tickers ReDim y(1 To UBound(Ticker) + 4, 1 To 9) ' Loop through the unique tickers For i = LBound(Ticker) To UBound(Ticker) ' Reset the variables for each iteration of the loop iii = iii + 1: iv = 0: v = 0 lBuy = 0: lSell = 0 ' Loop through all the rows in array x (start at 2 to ignore headers) For ii = 2 To UBound(x, 1) ' Check if the Ticker of the second loop is the same as the first loop If x(ii, 1) = Ticker(i) Then ' If it is then start loading array y y(iii, 1) = x(ii, 1) ' Check if it is "Buy" or "Sell" If x(ii, 2) = "Buy" Then '****CHANGE "BUY" TO PERSIAN SCRIPT**** ' Builds up the "Buy Volume" for each time a Ticker is encountered y(iii, 2) = y(iii, 2) + x(ii, 3) ' Because "Average Buying Price" is an average ' it needs to be built differently, to avoid dividing by zero ' lBuy is a variable that the Cost gets added to lBuy = lBuy + x(ii, 4): iv = iv + 1 ' If not "Buy" then must be "Sell" ' Sell works in exactly the same way as Buy Else y(iii, 5) = y(iii, 5) + x(ii, 3) lSell = lSell + x(ii, 4): v = v + 1 End If ' Now the "Average Buying Price" and "Average Sell Price" ' Can be calculated and added to the array y If iv > 0 Then y(iii, 4) = lBuy / iv: y(iii, 3) = y(iii, 2) * y(iii, 4) End If If v > 0 Then y(iii, 7) = lSell / v: y(iii, 6) = y(iii, 5) * y(iii, 7) End If End If Next ' Update the "Inventory" and "Profit/Loss" for each Ticker y(iii, 8) = y(iii, 8) + (y(iii, 2) - y(iii, 5)) y(iii, 9) = y(iii, 5) * (y(iii, 7) - y(iii, 4)) ' If "Inventory" is negative then must be a Short Sell so font changes to red as a warning If y(iii, 8) < 0 Then Cells(iii + 1, 8).Font.Color = vbRed ' d is a variable that gets built to find the total "Cost" of all Transactions d = d + y(iii, 3) Next ' After First Loop has run through all Tickers the total "Cost" is added to array y y(UBound(y, 1), 3) = d ' The Inventory sheet is cleared of any existing data ' Then the contents of array y are placed on the sheet ' The data then gets formatted correctly With Sheet2 .Cells(1).CurrentRegion.Offset(1).Clear .Cells(2, 1).Resize(UBound(y, 1), 9) = y .UsedRange.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)" .UsedRange.Columns(8).NumberFormat = "#,##0_ ;[Red]-#,##0 " .Activate End With End Sub
Thank you