Help wih complex formula or macro or solution

[Sal Young]

Thanks for your reply.

You're correct, there is a lot to guess from my question because this is a
very complex problem. Let me try to explain a little bit better. I'm
including two comma delimited sets of records that you'll need to import into
two spreadsheets. The first set is what the user will have in their
spreadsheet and the second set is what the end result should look like. Your
recommended solution will not work because it will generate a running total
and what I'm looking for is a first in first out type of operation that will
allow the user to see the Gain/Loss per open/close record.

maybe you can provide me with an email where I can send you the Excel
spreadsheet.

Here's a sample of the original data:
ID,QTY,CODE,Amount,
1,2000,C,285310,
2,1000,C,143630,
3,-1000,V,144975,
4,1000,C,143990,
5,-1000,V,144960,
6,-1000,V,145774,
7,2000,C,285720,
8,-1000,V,145762.4,
9,1000,C,145610,
10,1000,C,145380,
11,1000,C,145880,
12,-1000,V,145598,
13,-1000,V,145454.18,
14,1000,C,143905,
15,-1000,V,145705,
16,2000,C,286540
17,-1000,V,145595
18,-1000,V,145710.64
19,1000,C,145425
20,-1000,V,145702
21,-1000,V,145750
22,1000,C,143390
23,-1000,V,145827.72
24,-1000,V,145903
25,2000,C,285220
26,-1000,V,145702.55
27,-1000,V,145625
28,-1000,V,145738
29,1000,C,145525
30,-1000,V,144975


Here's a sample of the desire goal:
ID,QTY,CODE,Amount,Unit Price,OpenID,CloseID,Gain/Loss
1,2000,C,285310,142.655,1,3,2320
2,1000,C,143630,143.63,1,5,2305
3,-1000,V,144975,-144.975,2,6,2144
4,1000,C,143990,143.99,4,8,1772.4
5,-1000,V,144960,-144.96,7,12,2738
6,-1000,V,145774,-145.774,7,13,2594.18
7,2000,C,285720,142.86,9,15,95
8,-1000,V,145762.4,-145.7624,10,17,215
9,1000,C,145610,145.61,11,18,-169.36
10,1000,C,145380,145.38,14,20,1797
11,1000,C,145880,145.88,16,21,2480
12,-1000,V,145598,-145.598,16,23,2557.72
13,-1000,V,145454.18,-145.45418,19,24,478
14,1000,C,143905,143.905,22,26,2312.55
15,-1000,V,145705,-145.705,25,27,3015
16,2000,C,286540,143.27,25,28,3128
17,-1000,V,145595,-145.595,29,30,-550
18,-1000,V,145710.64,-145.71064,,,
19,1000,C,145425,145.425,,,
20,-1000,V,145702,-145.702,,,
21,-1000,V,145750,-145.75,,,
22,1000,C,143390,143.39,,,
23,-1000,V,145827.72,-145.82772,,,
24,-1000,V,145903,-145.903,,,
25,2000,C,285220,142.61,,,
26,-1000,V,145702.55,-145.70255,,,
27,-1000,V,145625,-145.625,,,
28,-1000,V,145738,-145.738,,,
29,1000,C,145525,145.525,,,
30,-1000,V,144975,-144.975,,,

Sal:

I'll take a shot at this though I must say that your question
leaves much to guess at. If this "Table" resides in cells(A1:E5) then
your formulas would be as follows for cells(C2:E5)

C2:=A2*B2
C3:=A3*B3
C4:=A4*B4
C5:=A5*B5

D2:=A2
D3:=SUM(D2,A3)
D4:=SUM(D3,A4)
D5:=SUM(D4,A5)

E2:=C2
E3:=SUM(E2,C3)
E4:=SUM(E3,C4)
E5:=SUM(E4,C5)

Once you have the formulas in place, you can just autofill them down
the length of your sheet and they should do what it is I think you are
asking of it. Lemme know if I got it right or missed the point
completely...Mark

I need to write some kind of balance sheet where the balance is calculated
based on how much a quantity of one record covers the quantity of another
record with the opposite sign. Here's an example.

QTY UNITCOST TotCost BalQTY BalAMT
500 200 100,000 500 100,000
-100 210 -21,000 400 79,000
500 205 102,500 900 181,500
-600 210 -126,000 300 55,500


The columns & values I have to generate are the BalQTY and BalAMT. I wan to
point out that a record may close multiple records as demonstrated with the
first record closing record 2 (qty 100) and partially record 4 (qty 400).

 

[Richard Buttrey]

For clarification, can you explain how you derive the Unit Price -->
onwards data columns in the goal sheet.

e.g. where does the

Unit Price,OpenID,CloseID,Gain/Loss
142.655,1,3,2320

data in the first record come from?

Rgds

Thanks for your reply.

You're correct, there is a lot to guess from my question because this is a
very complex problem. Let me try to explain a little bit better. I'm
including two comma delimited sets of records that you'll need to import into
two spreadsheets. The first set is what the user will have in their
spreadsheet and the second set is what the end result should look like. Your
recommended solution will not work because it will generate a running total
and what I'm looking for is a first in first out type of operation that will
allow the user to see the Gain/Loss per open/close record.

maybe you can provide me with an email where I can send you the Excel
spreadsheet.

Here's a sample of the original data:
ID,QTY,CODE,Amount,
1,2000,C,285310,
2,1000,C,143630,
3,-1000,V,144975,
4,1000,C,143990,
5,-1000,V,144960,
6,-1000,V,145774,
7,2000,C,285720,
8,-1000,V,145762.4,
9,1000,C,145610,
10,1000,C,145380,
11,1000,C,145880,
12,-1000,V,145598,
13,-1000,V,145454.18,
14,1000,C,143905,
15,-1000,V,145705,
16,2000,C,286540
17,-1000,V,145595
18,-1000,V,145710.64
19,1000,C,145425
20,-1000,V,145702
21,-1000,V,145750
22,1000,C,143390
23,-1000,V,145827.72
24,-1000,V,145903
25,2000,C,285220
26,-1000,V,145702.55
27,-1000,V,145625
28,-1000,V,145738
29,1000,C,145525
30,-1000,V,144975


Here's a sample of the desire goal:
ID,QTY,CODE,Amount,Unit Price,OpenID,CloseID,Gain/Loss
1,2000,C,285310,142.655,1,3,2320
2,1000,C,143630,143.63,1,5,2305
3,-1000,V,144975,-144.975,2,6,2144
4,1000,C,143990,143.99,4,8,1772.4
5,-1000,V,144960,-144.96,7,12,2738
6,-1000,V,145774,-145.774,7,13,2594.18
7,2000,C,285720,142.86,9,15,95
8,-1000,V,145762.4,-145.7624,10,17,215
9,1000,C,145610,145.61,11,18,-169.36
10,1000,C,145380,145.38,14,20,1797
11,1000,C,145880,145.88,16,21,2480
12,-1000,V,145598,-145.598,16,23,2557.72
13,-1000,V,145454.18,-145.45418,19,24,478
14,1000,C,143905,143.905,22,26,2312.55
15,-1000,V,145705,-145.705,25,27,3015
16,2000,C,286540,143.27,25,28,3128
17,-1000,V,145595,-145.595,29,30,-550
18,-1000,V,145710.64,-145.71064,,,
19,1000,C,145425,145.425,,,
20,-1000,V,145702,-145.702,,,
21,-1000,V,145750,-145.75,,,
22,1000,C,143390,143.39,,,
23,-1000,V,145827.72,-145.82772,,,
24,-1000,V,145903,-145.903,,,
25,2000,C,285220,142.61,,,
26,-1000,V,145702.55,-145.70255,,,
27,-1000,V,145625,-145.625,,,
28,-1000,V,145738,-145.738,,,
29,1000,C,145525,145.525,,,
30,-1000,V,144975,-144.975,,,





I need to write some kind of balance sheet where the balance is calculated
based on how much a quantity of one record covers the quantity of another
record with the opposite sign. Here's an example.

QTY UNITCOST TotCost BalQTY BalAMT
500 200 100,000 500 100,000
-100 210 -21,000 400 79,000
500 205 102,500 900 181,500
-600 210 -126,000 300 55,500


The columns & values I have to generate are the BalQTY and BalAMT. I wan to
point out that a record may close multiple records as demonstrated with the
first record closing record 2 (qty 100) and partially record 4 (qty 400).

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Sal Young]

Richard,

The unit price is 285310 (Amount) / 2000 (QTY)

 

[Richard Buttrey]

And what about the, Open ID, Close ID, and Gain/Loss columns of data.

I can sort of see where the Gain/Loss numbers come from, but what's
driving the choice of the Open/Close IDs? i.e why 1-3, 1-5, 2-6, 4-8
etc.? What's the rule for deriving those bands?

Rgds

Richard,

The unit price is 285310 (Amount) / 2000 (QTY)

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Sal Young]

Your first record will always set the SIGN of the OpenIDs. In this example,
all positive (QTY) numbers will be OpenID. If the first record in the
spreadsheet was a negative number; then, all negative numbers will be
OpenIDs. So, all positive QTY are OpenID & all negative QTY are CloseID in
this example.

Now that you have your first OpenID, you'll match to the first record (1)
with the first record that has an opposite SIGN which in this case is record
3. After these two records are matched, record 1 still has a balance QTY of
1000 which needs to be matched to the next record with the opposite SIGN in
this case, record 5. This will leave record 1 with a balance QTY of 0 and
record 1 matched to 3 & 5. You'll move to the next OpenID which is 2 because
it has a positive QTY & does it all over again until you run out of records.

 

[Richard]

Sal, a quick update.

I'm just finishing off a potential solution. I'm out of the office at
the moment but hope to get a VBA macro to you tomorrow - Thursday.

Rgds

 

[Richard Buttrey]

Sal,

I'm posting below 5 procedures that will produce the summary you're
after.

The assumption is that the 4 columns of original data have been loaded
into A131 using the example data below. (I used the DATA Text to
Coumns, after I'd posted your CSV details into column A)

The solution will be shown in cols G:N ( Obviously if you want to
change the positioning you'll have to adjust the code)

You should attach the first procedure (GainLossCalc) to a macro button

Rgds

Sub GainLossCalc()
Call CopyData
Call UnitPrice
Call OpenCloseID
Call GainLoss
Call TidyUp
End Sub

Sub CopyData()
[A1].CurrentRegion.Copy Destination:=Range("G1")
[F1] = "IDUsed": [K1] = "Unit Price": [L1] = "Open ID"
[M1] = "Close ID": [N1] = "Gain / Loss"
End Sub

Sub UnitPrice()
[K2] = "=j2/h2"
[K2].Copy
Range([J3], [J3].End(xlDown)).Offset(0, 1).PasteSpecial
(xlPasteAll)
End Sub

Sub OpenCloseID()
Dim stFirst As String
Dim iOpenID As Integer
Dim x As Integer, y As Integer
Dim iBal As Integer
Dim iCurRow As Integer

If [B2] > 0 Then stFirst = "+" Else stFirst = "-"

For x = 1 To [A1].CurrentRegion.Rows.Count

If [B2].Cells(x, 1) > 0 And stFirst = "+" Then
iOpenID = x
iBal = [B2].Cells(x, 1)
End If
If [B2].Cells(x, 1) < 0 And stFirst = "-" Then
iOpenID = x
iBal = [B2].Cells(x, 1)
End If

y = 1
Do While iBal <> 0
If (stFirst = "+" And [B2].Cells(x + y) < 0 _
Or stFirst = "-" And [B2].Cells(x + y) > 0) _
And [B2].Cells(x + y).Offset(0, 4) <> "X" Then
iBal = iBal + [B2].Cells(x + y)
[B2].Offset(x + y - 1, 4) = "X"
[L65536].End(xlUp).Offset(1, 0) = iOpenID
[M65536].End(xlUp).Offset(1, 0) = x + y
End If
y = y + 1
Loop
Next
End Sub

Sub GainLoss()
[N2] =
"=-VLOOKUP(L2,A,4)/VLOOKUP(L2,A,2)*1000-VLOOKUP(M2,A,4)" _
& "/VLOOKUP(M2,A,2)*1000"
[N2].Copy
Range([M3], [M3].End(xlDown)).Offset(0, 1).PasteSpecial
(xlPasteAll)
End Sub

Sub TidyUp()
[F:F].ClearContents
[G1].CurrentRegion.Copy: [G1].PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub







__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________