FIFO-GAIN-STOCK PROBLEM

[pulvi]

Can anyone help with the problem of calculating gains as shown in "gain" sheet from the transactions of purchase and sale as shown in "trans" sheet and also the stock remaining thereafter as shown in the "stock" sheet. The solution can be either excel formulae and/or VBA code. The qty sold should be adjusted on First in First out basis (FIFO).

[SamT]

Here's an example of a Class Module of a FIFO from another VBAX thread I am currently working on.

Compiles, but not tested.

Option Explicit
Option Base 1

'Stores a First In, First Out array of Data Inputs.
'Data input is an aray of the current relevant BA values for this Brand
'Returns a Jagged Array, (an Array of Arrays)

Private Dict As Object
Private pReady As Boolean
Private pMaxSize As Long
Dim i As Long


'***Write Only Properties
Public Property Let MaxSize(MaximumSizeOfArray As Long)
  pMaxSize = MaximumSizeOfArray
End Property


Public Property Let DataInput(DataArrayForThisBrand As Variant)
i = i + 1
Dict.Add i, DataArrayForThisBrand
RemoveFirstItem
End Property

'*** Read only Properties
Property Get DataOutput() As Variant
  DataOutput = Dict.Items
End Property

Private Sub RemoveFirstItem()
  Do While Dict.Count > pMaxSize
    Dict.Keys()(1).Remove
  Loop
End Sub



Private Sub Class_Initialize()
Set Dict = CreateObject("scripting.dictionary")
End Sub

Private Sub Class_Terminate()
Set Dict = Nothing
End Sub