Calculating differences in exchange rates - FIFO method VBA

[Gini88]

Hello,


i'm trying to automate calculating differences in exchange rates - FIFO method. The problem is that I don't know how to handle with the situation, where transactions are not equal.

15996


Sub FIFO()

Dim suma_in As Double
Dim suma_out As Double
Dim left_in As Double
Dim left_out As Double

'row indexes
Dim i As Integer
Dim j As Integer
Dim k As Integer

'column indexes
Dim kolumna_in As Integer
Dim kolumna_out As Integer


For i = 2 To Sheets("Arkusz2").Range("C65536").End(xlUp).Row ' goes through the in transactions
left_in = (Sheets("Arkusz2").Range("C" & i).Value) ' calculates how much money is left
j = 2
k = 2

suma_in = (Sheets("Arkusz2").Range("C" & j)) * (Sheets("Arkusz2").Range("A" & j).Value) 'multiplies the amount by exchange rate

kolumna_in = 0
kolumna_out = 1

If left_in >= left_out Then 'checks if in>out

suma_out = Abs(Sheets("Arkusz3").Range("A" & k).Value)


'lists in the next columns the amount of calculated transactions
Sheets("Arkusz2").Range("D" & j).Offset(0, kolumna_in + 1) = suma_out * (Sheets("Arkusz2").Range("A" & j).Value)
Sheets("Arkusz3").Range("C" & k).Value = suma_out * Sheets("Arkusz2").Range("A" & j).Value
left_in = left_in + suma_out
left_out = suma_out - left_in
k = k + 1
kolumna_in = kolumna_in + 1
kolumna_out = kolumna_out + 1


'otherwise calculates analogically
Else
suma_in = Abs(Sheets("Arkusz2").Range("C" & j).Value)
Sheets("Arkusz2").Range("D" & j) = suma_in * (Sheets("Arkusz2").Range("A" & j).Value)
Sheets("Arkusz3").Range("C" & k).Offset(0, kolumna_out + 1) = suma_in * Sheets("Arkusz2").Range("A" & j).Value
left_in = left_in + suma_out
left_out = suma_out - left_in
j = j + 1
kolumna_in = kolumna_in + 1
kolumna_out = kolumna_out + 1
End If




Next i
End Sub

[SamT]

The attachment does not seem to be an Excel file. Internally, it has a [content type = xml], but seems to be a binary file.

[Gini88]

It is an xlsx..

[Gini88]

I have also another idea. This solution works properly for the first transaction, but I don't exactly know how to go through all transactions..



Sub FIFO()


Dim left_in As Double
Dim left_out As Double
Dim pom As Double
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
j = 2
k = 2
l = 2






If Sheets("Arkusz3").Range("A" & k) + Sheets("Arkusz2").Range("C" & j).Value < 0 Then


Sheets("Arkusz2").Range("E" & j).Value = Sheets("Arkusz2").Range("C" & j).Value * (-1) * Sheets("Arkusz2").Range("A" & j).Value
Sheets("Arkusz3").Range("B" & k).Offset(0, 1) = Sheets("Arkusz2").Range("C" & j).Value * Sheets("Arkusz2").Range("A" & j).Value
left_out = Sheets("Arkusz3").Range("A" & k).Value + Sheets("Arkusz2").Range("C" & j).Value
left_in = 0
j = j + 1


Else


Sheets("Arkusz2").Range("E" & j).Offset(0, 1) = Sheets("Arkusz3").Range("A" & j).Value * Sheets("Arkusz2").Range("A" & j).Value
Sheets("Arkusz3").Range("C" & k) = Sheets("Arkusz3").Range("A" & j).Value * (-1) * Sheets("Arkusz2").Range("A" & j).Value
left_in = Sheets("Arkusz3").Range("A" & k).Value + Sheets("Arkusz2").Range("C" & j).Value
left_out = 0
k = k + 1


End If


If left_out < 0 Then


Do While left_out < 0


'For j = 2 To Sheets("Arkusz2").Range("C65536").End(xlUp).Row
'left_out = Sheets("Arkusz3").Range("A" & k)
Sheets("Arkusz2").Range("E" & j).Value = Sheets("Arkusz2").Range("C" & j).Value * (-1) * Sheets("Arkusz2").Range("A" & j).Value
Sheets("Arkusz3").Range("B" & k).Offset(0, l) = Sheets("Arkusz2").Range("E" & j).Value * (-1)
left_out = left_out - (Sheets("Arkusz2").Range("E" & j).Value / Sheets("Arkusz2").Range("A" & j).Value)
left_in = 0
j = j + 1
l = l + 1


Loop
i = k
Sheets("Arkusz3").Range("B" & k).Offset(0, l - 1) = (Sheets("Arkusz2").Range("C" & j - 1).Value - left_out) * Sheets("Arkusz2").Range("A" & j - 1).Value
Sheets("Arkusz2").Range("E" & j - 1).Offset(0, 1) = (left_out * Sheets("Arkusz2").Range("A" & j - 1).Value) * (-1) '+Sheets("Arkusz2").Range("D" & j - 1).Value
Sheets("Arkusz2").Range("E" & j - 1) = (Sheets("Arkusz2").Range("C" & j - 1).Value - left_out) * Sheets("Arkusz2").Range("A" & j - 1).Value * (-1)


'Next
Else
Do While left_in > 0


Sheets("Arkusz3").Range("C" & k) = Sheets("Arkusz3").Range("A" & k).Value * Sheets("Arkusz2").Range("A" & j).Value * (-1)
Sheets("Arkusz2").Range("E" & j).Offset(0, l) = Sheets("Arkusz3").Range("C" & k) * (-1)
left_in = Sheets("Arkusz3").Range("A" & k).Value + left_in
left_out = 0
l = l + 1
k = k + 1
'Next
Loop
i = j
Sheets("Arkusz2").Range("E" & j).Offset(0, l - 1) = (Sheets("Arkusz3").Range("A" & k - 1).Value - left_in) * Sheets("Arkusz2").Range("A" & j).Value
Sheets("Arkusz3").Range("C" & k - 1).Offset(0, 1) = (left_in * Sheets("Arkusz2").Range("A" & j).Value)
Sheets("Arkusz3").Range("C" & k - 1) = (Sheets("Arkusz3").Range("A" & k - 1).Value - left_in) * Sheets("Arkusz2").Range("A" & j).Value * (-1)
End If
End Sub