x
Solved

# Basic logic

Posted on 2001-09-02
Medium Priority
212 Views
Hello i work for a broking house.

I need to calculate what is called the open
theory pricen or cross match of a stock.

on the stock exchange there  is something called the depth of market.

The depth of market is a list of all
the different prices (and the volume) people want to buy the stock at

and a list of all the different prices (and the volume) people want to sell
the stock at.

1      1   30000 0.180
2      1   18251 0.150
3      1   25000 0.120

Price Quantity #Sell Level
0.200 16000     2    1
0.250 3000      1    2
0.400 10000     1    3

I need some VBBasic code that will do what is explained at this url.

http://www.asx.com.au/markets/l4/openclose_am4.htm

Thanks you very much for any help.

Weg

0
Question by:wegwong
• 19
• 15
• 2

Expert Comment

ID: 6449638
Hi
I couldnt understand what is the method for the calculations.But i think the VBA with Spread sheets is the best solution for ur problem .You can make Calculations easily through the use of spread sheets.
Thank u
Kamal
0

Author Comment

ID: 6449664
Hello Mr Kamal,

Thats is what I am trying to work out, a method( BASIC code)
to calculate the cross match.

based on the information given at

http://www.asx.com.au/markets/l4/openclose_am4.htm

If you can do it in Excel VBA, that
woulb be of great help.

However I would prefer to use VB
with grid controls or arrays to hold the data

thanks
weg
0

Expert Comment

ID: 6449875
Hi
but i cant get ur method .i mean the basic method u r using for calculation.
i dont know anything about the shares .if u want basic code in vbexcel then its like:
public appexcel as application
dim wsheet as worksheet
dim wbook as workbook
set appexcel=createobject("Excel.Application")
set wbook=getobject("file name in excel")
set wsheet=wbook.sheets(sheet no)
wshhet.cells.value()
and to calculate anything in excel:
appexcel.evaluate(expression)
and if still u have any doubt u can send the details of ur method so that i can understand them only then i can help u further.
thanking you
ms. kamal
0

LVL 4

Expert Comment

ID: 6451413
Basic Logic.... hehehe.

This is a good one.

What makes one a priority?

I'll start with that question and continue after this is answered and after I study the example a little more.

Thanks - Wileecoy.
0

LVL 4

Expert Comment

ID: 6451699
Ok - what happens when there is no priority overlap, but when latter trades would overlap?

For example:

A. Sell 400 @ 243
B. Sell 600 @ 244
C. Sell 300 @ 242

Now - If I understand priority properly, you start with the top of each list and offset them as long as you can.

However, if you look down to the 'Buy B' and 'Sell C', you can clearly make a transaction.

Per the link that you listed, if there is no overlap, the opening price is the price of the first trade of the day.  I assume that is also based on priority.  In this case, opening price would be at \$240 even though B and C are higher prices.

A few more 'rules' answered and I will have something for you.

Thanks - Wileecoy
0

Author Comment

ID: 6452023
Hi wileecoy , thanks very much for the help.

The price on the buyers side
is sorted decending. I.e the highest
price that someone is willing to
pay for the stock will always be at the'top' of the buy list and is called
the priority price.

The price on the sellers side is sorted
asscending. I.e. The lowest price someone is willing
to sell the stock at. The lowest sell price will
always be on the 'top' of the sell list and is called
the priority  sell price.

So in the example you have given

C. Buy 500 @ 243 C. Sell 300 @ 242
B. Buy 300 @ 242 A. Sell 400 @ 243
A. Buy 100 @ 240 b. Sell 600 @ 244

i think may not really actually happen.

Durning the normal course of the day the Exchange computers
will calacuate the matches.

When the market opens, the Exchanges computers transmit the open price.

What we are trying to do is calculate what the open price
will be before the exchange opens. When there may be an overlap.

wileecoy, I hope i havn't confused the issue.

Thanks again,
weg

0

LVL 4

Expert Comment

ID: 6452057
Ok - I couldn't wait.

I have a solution that gives the same opening price as the example at the link that you posted.

If that isn't the final solution, it is at least a good start - but I really beleive that it does exactly what you need.

It will be in the next comment.

The instructions are at the beginning.

hth.

Wileecoy
0

LVL 4

Expert Comment

ID: 6452064
Wow - I have the solution except for one thing.

I didn't see your message until I posted my last one.

The Ascending / Descending prices will slow me down a little.

Give me a few more minutes.

Wileecoy.
0

Author Comment

ID: 6452076
Thanks Wileecoy..

0

Author Comment

ID: 6452078
Thanks Wileecoy..

0

Author Comment

ID: 6452085
Thanks Wileecoy..

0

Author Comment

ID: 6452088
oops, don't know what happened, sorry.
0

LVL 4

Expert Comment

ID: 6452105
'************************************************************'
'* Norm Floria                                              *'
'* September 2001                                           *'
'* Calculates Market Open Price for a stock                 *'
'*                                                          *'
'*                                                          *'
'* calculation                                              *'
'*                                                          *'
'* http://www.asx.com.au/markets/l4/openclose_am4.htm       *'
'*                                                          *'
'* If you modify this free code, please send me a           *'
'*      copy at NormFloria@aol.com                          *'
'*                                                          *'
'* This 'very-beta' application carries no warranties,      *'
'* implied or otherwise, use at your own risk.              *'
'*                                                          *'
'* If you use this code, or any part of it include this     *'
'*    caption and please don't claim responsibility,        *'
'*    good or bad, for my work.  Thanks - Wileecoy          *'
'*                                                          *'
'* 1. Open a standard exe project                           *'
'* 2. add the following controls:                           *'
'*    a. One ComboBox (combo1)                              *'
'*    b. Four Labels (label1...label4)                      *'
'*    c. Three CommandButtons (command1...command3)         *'
'*    d. Two TextBoxes (text1, text2)                       *'
'*    e. Two MSFlexGrids (MSFlexGrid1, MSFlexGrid2)         *'
'*      (note: you will need to add in Project/Components)  *'
'*                                                          *'
'* You can add the controls where-ever you want as the      *'
'*     Form_Resize procedure puts them where they need      *'
'*     to be.                                               *'
'*                                                          *'
'************************************************************'

Option Explicit

Dim iSell As Integer
Dim dOpenPrice As Double

Private Sub Command1_Click()
End Sub
Private Function GetRow(msf As MSFlexGrid, Price As Double, TranType As String)

'Sorting decending for buy and ascending for sell
'Just comparing our current price with each row until
' we find where to inser.

Dim i As Integer
i = 1
GetRow = 0
Do
If Price > msf.TextMatrix(i, 2) Then
GetRow = i
Exit Do
End If
i = i + 1
Loop Until GetRow <> 0
ElseIf TranType = "Sell" Then
Do
If Price < msf.TextMatrix(i, 2) Then
GetRow = i
Exit Do
End If
i = i + 1
Loop Until GetRow <> 0
Else
'This should never, ever happen
MsgBox "Invalid Transaction Type", vbOKOnly, "Wileecoy's Market Calc App"
Exit Function
End If

End Function
Private Sub Command2_Click()

'Posting the transaction to the grids
Else                            'This means that we are selling
iSell = GetRow(MSFlexGrid2, Text2.Text, "Sell")
MSFlexGrid2.AddItem "S" & iSell & vbTab & Text1.Text & vbTab & Text2.Text, iSell
ReDoPriority MSFlexGrid2, iSell, "Sell"
End If

Text1.Text = ""
Text2.Text = ""

'Remove Empty Row (it was required for the fixed row header)
With MSFlexGrid1
If .TextMatrix(.Rows - 1, 1) = "" Then
.RemoveItem (.Rows)
End If
End With

With MSFlexGrid2
If .TextMatrix(.Rows - 1, 1) = "" Then
.RemoveItem (.Rows)
End If
End With

End Sub
Private Sub ReDoPriority(msf As MSFlexGrid, Row As Integer, TransType As String)
Dim i As Integer
i = Row
msf.Col = 0

For i = i To msf.Rows - 1
msf.Row = i
msf.Text = "B" & i
Else
msf.Text = "S" & i
End If
Next

End Sub

Private Function Is_There_Overlap() As Boolean

Dim dSell As Double

dSell = Val(MSFlexGrid2.TextMatrix(1, 2))

Is_There_Overlap = True
Else
Is_There_Overlap = False
End If

End Function

Private Sub Command3_Click()
Dim sTemp As String

With MSFlexGrid1
If .TextMatrix(.Rows - 1, 1) = "" Then
.RemoveItem (.Rows)
End If
End With

With MSFlexGrid2
If .TextMatrix(.Rows - 1, 1) = "" Then
.RemoveItem (.Rows)
End If
End With

If Is_There_Overlap = True Then
Calc_Overlap
Else
dOpenPrice = Val(MSFlexGrid1.TextMatrix(1, 2))
Label4.Caption = "Opening price is " & Format(dOpenPrice, "\$#,###,##0.00")
End If

End Sub
Private Sub Calc_Overlap()

Dim i As Integer

'For moving the appropriate FlexGrid to the next row
Dim MSFMoveNext As MSFlexGrid
Dim MSF1 As Integer
Dim MSF2 As Integer

'To save the last Order
Dim iSellOrderQty As Integer
Dim dSellOrderPrice As Double

'To Store Order Price
Dim dOpenPrice As Double

Dim iBuyNum As Integer     'Number of Shares
Dim dBuyPrice As Double    'Price of shares

'Variables for Sell Side
Dim sSellP As String        'Priority
Dim iSellNum As Integer     'Number of Shares
Dim dSellPrice As Double    'Price of shares

i = 1

With MSFlexGrid1
iBuyNum = Val(.TextMatrix(i, 1)) 'This is the # of Shares
dBuyPrice = Val(.TextMatrix(i, 2)) 'This is the price
End With

With MSFlexGrid2
sSellP = .TextMatrix(i, 0) 'This is the Buy/Sell Code
iSellNum = Val(.TextMatrix(i, 1)) 'This is the # of Shares
dSellPrice = Val(.TextMatrix(i, 2)) 'This is the price
End With

MSF1 = 1
MSF2 = 1

Do

'Here we store the final Order
iSellOrderQty = iSellNum
dSellOrderPrice = dSellPrice

'Now we need to keep offsetting quantities until there is no overlap
iSellNum = 0
Set MSFMoveNext = MSFlexGrid2           'so we know which one to move to next row
MSF2 = MSF2 + 1                         'set row counter down one
i = MSF2                                'set variable equal to desired row
MSF1 = MSF1 + 1
End If
Else
Set MSFMoveNext = MSFlexGrid1
MSF1 = MSF1 + 1
i = MSF1
If iSellNum = 0 Then
MSF2 = MSF2 + 1
End If
End If
Else
'Now we need to do the calculation
Label4.Caption = "Opening price is " & Format(dOpenPrice, "\$#,###,##0.00")
Exit Do
End If

'Here we move the appropriate Grid to the next row
MoveNext:
With MSFMoveNext
iBuyNum = Val(.TextMatrix(MSF1, 1)) 'This is the # of Shares
dBuyPrice = Val(.TextMatrix(MSF1, 2)) 'This is the price
Else
sSellP = .TextMatrix(MSF2, 0) 'This is the Buy/Sell Code
iSellNum = Val(.TextMatrix(MSF2, 1)) 'This is the # of Shares
dSellPrice = Val(.TextMatrix(MSF2, 2)) 'This is the price
End If
End With

Set MSFMoveNext = MSFlexGrid1
i = MSF1
GoTo MoveNext
ElseIf iSellNum = 0 Then
Set MSFMoveNext = MSFlexGrid2
i = MSF2
GoTo MoveNext
End If

Loop

Set MSFMoveNext = Nothing

End Sub

Label1.BorderStyle = 1
Label1.Alignment = vbCenter
Label1.Caption = "Trans Type"

Label2.BorderStyle = 1
Label2.Alignment = vbCenter
Label2.Caption = "# of Shares"

Label3.BorderStyle = 1
Label3.Alignment = vbCenter
Label3.Caption = "Share Price"

Label4.Alignment = 0
Label4.Caption = ""

Combo1.Clear
Combo1.ListIndex = 0

Command1.Caption = "Exit"
Command2.Caption = "Post Order"
Command3.Caption = "Calculate Prices"

Text1.Text = ""
Text2.Text = ""

Dim varArray As Variant
varArray = Array("Order", "Qty", "Price")
With MSFlexGrid1
.Clear
.AllowBigSelection = False
.Rows = 0
.Cols = UBound(varArray) + 1
.FixedRows = 1
.Row = 0
.Col = 0
End With

With MSFlexGrid2
.Clear
.AllowBigSelection = False
.Rows = 0
.Cols = UBound(varArray) + 1
.FixedRows = 1
.Row = 0
.Col = 0
End With

With MSFlexGrid1
If .TextMatrix(.Rows - 1, 1) = "" Then
.RemoveItem (.Rows)
End If
End With

With MSFlexGrid2
If .TextMatrix(.Rows - 1, 1) = "" Then
.RemoveItem (.Rows)
End If
End With

End Sub

With MSFlexGrid1
.AddItem "B1" & vbTab & "400" & vbTab & "424", .Rows - 1
.AddItem "B2" & vbTab & "2000" & vbTab & "423", .Rows - 1
.AddItem "B3" & vbTab & "10000" & vbTab & "421", .Rows - 1
.AddItem "B4" & vbTab & "5000" & vbTab & "420", .Rows - 1
End With

With MSFlexGrid2
.AddItem "S1" & vbTab & "1000" & vbTab & "420", .Rows - 1
.AddItem "S2" & vbTab & "1000" & vbTab & "421", .Rows - 1
.AddItem "S3" & vbTab & "600" & vbTab & "424", .Rows - 1
End With

End Sub

Private Sub Form_Resize()

Dim ctr As Control

For Each ctr In Form1
Debug.Print ctr.Name & ".top = form1.height * " & (ctr.Top / Form1.Height)
Debug.Print ctr.Name & ".left = form1.width * " & (ctr.Left / Form1.Width)
Debug.Print ctr.Name & ".height = form1.height * " & (ctr.Height / Form1.Height)
Debug.Print ctr.Name & ".width = form1.width * " & (ctr.Width / Form1.Width)
Debug.Print ctr.Name & ".fontsize = 7.8 * form1.width * form1.height / " & (Form1.Width * Form1.Height)
Next

Command3.Top = Form1.Height * 0.8189655
Command3.Left = Form1.Width * 0.009202454
Command3.Height = Form1.Height * 0.07183908
Command3.Width = Form1.Width * 0.190184
Command3.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Command2.Top = Form1.Height * 0.06034483
Command2.Left = Form1.Width * 0.4509203
Command2.Height = Form1.Height * 0.07183908
Command2.Width = Form1.Width * 0.1763804
Command2.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Text2.Top = Form1.Height * 0.06034483
Text2.Left = Form1.Width * 0.3174847
Text2.Height = Form1.Height * 0.06896552
Text2.Width = Form1.Width * 0.1211656
Text2.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Combo1.Top = Form1.Height * 0.06034483
Combo1.Left = Form1.Width * 0.009202454
'Combo1.Height = Form1.Height * 0.06896552
Combo1.Width = Form1.Width * 0.1533742
Combo1.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Text1.Top = Form1.Height * 0.06034483
Text1.Left = Form1.Width * 0.1748466
Text1.Height = Form1.Height * 0.06896552
Text1.Width = Form1.Width * 0.1303681
Text1.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Command1.Top = Form1.Height * 0.06034483
Command1.Left = Form1.Width * 0.8006135
Command1.Height = Form1.Height * 0.07183908
Command1.Width = Form1.Width * 0.1763804
Command1.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
MSFlexGrid1.Top = Form1.Height * 0.1637931
MSFlexGrid1.Left = Form1.Width * 0.009202454
MSFlexGrid1.Height = Form1.Height * 0.6321839
MSFlexGrid1.Width = Form1.Width * 0.4800614
MSFlexGrid1.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
MSFlexGrid2.Top = Form1.Height * 0.1637931
MSFlexGrid2.Left = Form1.Width * 0.4969325
MSFlexGrid2.Height = Form1.Height * 0.6321839
MSFlexGrid2.Width = Form1.Width * 0.4800614
MSFlexGrid2.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Label4.Top = Form1.Height * 0.8275862
Label4.Left = Form1.Width * 0.2162577
Label4.Height = Form1.Height * 0.0545977
Label4.Width = Form1.Width * 0.2684049
Label4.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Label3.Top = Form1.Height * 0.00862069
Label3.Left = Form1.Width * 0.3174847
Label3.Height = Form1.Height * 0.0545977
Label3.Width = Form1.Width * 0.1211656
Label3.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Label2.Top = Form1.Height * 0.00862069
Label2.Left = Form1.Width * 0.1748466
Label2.Height = Form1.Height * 0.0545977
Label2.Width = Form1.Width * 0.1303681
Label2.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#
Label1.Top = Form1.Height * 0.00862069
Label1.Left = Form1.Width * 0.009202454
Label1.Height = Form1.Height * 0.0545977
Label1.Width = Form1.Width * 0.1533742
Label1.FontSize = 7.8 * Form1.Width * Form1.Height / 32673020#

End Sub
0

LVL 4

Expert Comment

ID: 6452113
The instructions are in the header text of the form.

Just paste all of that into Form1 of a standard project.

Add all of the controls and press F5.

Oh - don't worry about trying to figure out where the controls go.  I knew that would be a problem, so I added all of that to the resize procedure.

All you have to do is get the components on the form and keep their default name and the code will do all the rest.

Let me know how this works.  I didn't do a lot of formatting or 'cosmetic' work.  And I also did a lot more than what you asked for, but I had to to work myself through the problem.

HTH.

Wileecoy.
0

LVL 4

Expert Comment

ID: 6452120
Sorry, one more comment.

In the Form_Resize procedure you can comment-out the following:

Dim ctr As Control

For Each ctr In Form1
Debug.Print ctr.Name & ".top = form1.height * " & (ctr.Top / Form1.Height)
Debug.Print ctr.Name & ".left = form1.width * " & (ctr.Left / Form1.Width)
Debug.Print ctr.Name & ".height = form1.height * " & (ctr.Height / Form1.Height)
Debug.Print ctr.Name & ".width = form1.width * " & (ctr.Width / Form1.Width)
Debug.Print ctr.Name & ".fontsize = 7.8 * form1.width * form1.height / " & (Form1.Width * Form1.Height)
Next

You won't need that anymore.

Wileecoy.
0

Author Comment

ID: 6452142
Thanks Wileecoy. Looks good. Sorry to be a
pain but would u mind sending the project file to
me a wegwong@hotmail.com

I give it a go.

Thanks.
0

Author Comment

ID: 6452148
Thanks Wileecoy. Looks good. Sorry to be a
pain but would u mind sending the project file to
me a wegwong@hotmail.com

I give it a go.

Thanks.
0

LVL 4

Expert Comment

ID: 6452153
0

Author Comment

ID: 6452157
Thanks Wileecoy. Looks good. Sorry to be a
pain but would u mind sending the project file to
me a wegwong@hotmail.com

I give it a go.

Thanks.
0

LVL 4

Expert Comment

ID: 6452160
Done.
0

Author Comment

ID: 6452227
Hi wileecoy,

Thanks for emailing the project files. And thanks for the very much appreciated  help.

I have run your example and it works perfectly.

I also run it through some other 'numbers'

With MSFlexGrid1
.AddItem "B1" & vbTab & "555" & vbTab & "1798", .Rows - 1
.AddItem "B2" & vbTab & "200" & vbTab & "1795", .Rows - 1
.AddItem "B3" & vbTab & "189" & vbTab & "1794", .Rows - 1
.AddItem "B4" & vbTab & "350" & vbTab & "1782", .Rows - 1
.AddItem "B4" & vbTab & "1000" & vbTab & "1770", .Rows - 1
.AddItem "B4" & vbTab & "282" & vbTab & "1770", .Rows - 1
.AddItem "B4" & vbTab & "550" & vbTab & "1760", .Rows - 1

.AddItem "B4" & vbTab & "1500" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "3000" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "286" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "2700" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "2000" & vbTab & "1750", .Rows - 1

End With

the above calculates to an open price of 1790.2

(

I changed the formatting of the number to

Label4.Caption = "Opening price is " & Format(dOpenPrice, "####0.0")

)

The above also has a match volume of 944.

Is it simple to obtain this figure? (the match volume).

I havn't had time to analyze  the code. I havn't had any experience with the MSFlexGrid  grid control.
( I use 'spread'.ocx from fpoint.com )

So I am figuring that one out.

Thanks again

weg

0

Author Comment

ID: 6452241
Hi wileecoy,

Thanks for emailing the project files. And thanks for the very much appreciated  help.

I have run your example and it works perfectly.

I also run it through some other 'numbers'

With MSFlexGrid1
.AddItem "B1" & vbTab & "555" & vbTab & "1798", .Rows - 1
.AddItem "B2" & vbTab & "200" & vbTab & "1795", .Rows - 1
.AddItem "B3" & vbTab & "189" & vbTab & "1794", .Rows - 1
.AddItem "B4" & vbTab & "350" & vbTab & "1782", .Rows - 1
.AddItem "B4" & vbTab & "1000" & vbTab & "1770", .Rows - 1
.AddItem "B4" & vbTab & "282" & vbTab & "1770", .Rows - 1
.AddItem "B4" & vbTab & "550" & vbTab & "1760", .Rows - 1

.AddItem "B4" & vbTab & "1500" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "3000" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "286" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "2700" & vbTab & "1751", .Rows - 1
.AddItem "B4" & vbTab & "2000" & vbTab & "1750", .Rows - 1

End With

the above calculates to an open price of 1790.2

(

I changed the formatting of the number to

Label4.Caption = "Opening price is " & Format(dOpenPrice, "####0.0")

)

The above also has a match volume of 944.

Is it simple to obtain this figure? (the match volume).

I havn't had time to analyze  the code. I havn't had any experience with the MSFlexGrid  grid control.
( I use 'spread'.ocx from fpoint.com )

So I am figuring that one out.

Thanks again

weg

0

LVL 4

Expert Comment

ID: 6453423
wegwong,

I have no idea how to find the match volume.  If you can find out, I can work it into the app.

Thanks - Wileecoy.

Also - I will look at the spread.ocx just for curiosity sake.
0

Author Comment

ID: 6454850

I think the match volume is the remainder.
Eg in the example given at

http://www.asx.com.au/markets/l4/openclose_am4.htm

"

SEATS matches orders as follows:

3. The remaining 1,400 for buy order B matches against sell order L, leaving order B with a buy quantity of 400.

0

Author Comment

ID: 6454851
Hi wileecoy

I think the match volume is the remainder.
Eg in the example given at

http://www.asx.com.au/markets/l4/openclose_am4.htm

"

SEATS matches orders as follows:

3. The remaining 1,400 for buy order B matches against sell order L, leaving order B with a buy quantity of 400.

400 is the match volume.

Thanks

weg
0

LVL 4

Expert Comment

ID: 6454884
OK - Let me put that in.

Thanks.

Wileecoy.
0

LVL 4

Expert Comment

ID: 6454991
Ok - in the Calc_Overlap procedure, there is a section that starts with the comment:

'Now we need to do the calculation

The following code goes into that section and replaces everything from the "Else" just before the comment, to the "End If" that is just before the comment:

'Here we move the appropriate Grid to the next row

Here is the code:

******** begin code Calc_Overlap

'Now we need to do the calculation
Dim iMatch As Integer
Dim sFavor As String
sFavor = ", Buy over Sell."
Else
sFavor = ", Sell over Buy."
End If
Label4.Caption = "Opening price is " & Format(dOpenPrice, "\$#,###,##0.00") & _
", Match volume is " & Format(iMatch, "#,###,##0.00") & sFavor
Exit Do

******** end code Calc_Overlap

What that will do is always show a positive number and tell you if it is positive buy or sell.

Positive buy is " Buy over Sell.", positive sell is the opposite (makes sense, huh).

Anyway - give this a whirl and let me know what you think.

Thanks.

Wileecoy!
0

LVL 4

Expert Comment

ID: 6454999
Sorry - One more thing.

You will have to change one line in the form_resize procedure.

The line changing Label4.Width needs to be changed to:

Label4.Width = Form1.Width * 0.7607362

That should be it.

Thanks.

Wileecoy.
0

Author Comment

ID: 6455269
0

Author Comment

ID: 6455279
Test, EE failed to post last message.
0

Author Comment

ID: 6455302
0

LVL 4

Accepted Solution

wileecoy earned 1200 total points
ID: 6455487
Match Volume is the actual volume of shares that are matched up to trade.

So - the total of Buy shares (by priority) that exceed the asking price of sell shares (by priority) would be the match volume.

I have adjusted the code in one procedure to allow for this.

******** begin code

Private Sub Calc_Overlap()

Dim i As Integer

'For moving the appropriate FlexGrid to the next row
Dim MSFMoveNext As MSFlexGrid
Dim MSF1 As Integer
Dim MSF2 As Integer

'To save the last Order
Dim iSellOrderQty As Long
Dim dSellOrderPrice As Double

'To Store Order Price
Dim dOpenPrice As Double

'To Store Match Volume Accumulators
Dim iSellTotal As Long

Dim iBuyNum As Long     'Number of Shares
Dim dBuyPrice As Double    'Price of shares

'Variables for Sell Side
Dim sSellP As String        'Priority
Dim iSellNum As Long     'Number of Shares
Dim dSellPrice As Double    'Price of shares

i = 1

With MSFlexGrid1
iBuyNum = Val(.TextMatrix(i, 1)) 'This is the # of Shares
dBuyPrice = Val(.TextMatrix(i, 2)) 'This is the price
End With

With MSFlexGrid2
sSellP = .TextMatrix(i, 0) 'This is the Buy/Sell Code
iSellNum = Val(.TextMatrix(i, 1)) 'This is the # of Shares
dSellPrice = Val(.TextMatrix(i, 2)) 'This is the price
End With

MSF1 = 1
MSF2 = 1

iSellTotal = iSellNum

Do

'Here we store the final Order
iSellOrderQty = iSellNum
dSellOrderPrice = dSellPrice

'Now we need to keep offsetting quantities until there is no overlap
iSellNum = 0
Set MSFMoveNext = MSFlexGrid2           'so we know which one to move to next row
MSF2 = MSF2 + 1                         'set row counter down one
i = MSF2                                'set variable equal to desired row
MSF1 = MSF1 + 1
End If
Else
Set MSFMoveNext = MSFlexGrid1
MSF1 = MSF1 + 1
i = MSF1
If iSellNum = 0 Then
MSF2 = MSF2 + 1
End If
End If
Else
'Now we need to do the calculation
Dim iMatch As Long
'Dim sFavor As String
'sFavor = ", Buy over Sell."
Else
iMatch = iSellTotal - (iSellOrderQty - iBuyOrderQty)
'sFavor = ", Sell over Buy."
End If
Label4.Caption = "Opening price is " & Format(dOpenPrice, "\$#,###,##0.00") & _
", Match volume is " & Format(iMatch, "#,###,##0.00") '& sFavor
Exit Do
End If

'Here we move the appropriate Grid to the next row
MoveNext:
With MSFMoveNext
iBuyNum = Val(.TextMatrix(MSF1, 1)) 'This is the # of Shares
dBuyPrice = Val(.TextMatrix(MSF1, 2)) 'This is the price
Else
sSellP = .TextMatrix(MSF2, 0) 'This is the Buy/Sell Code
iSellNum = Val(.TextMatrix(MSF2, 1)) 'This is the # of Shares
dSellPrice = Val(.TextMatrix(MSF2, 2)) 'This is the price
iSellTotal = iSellTotal + iSellNum
End If
End With

Set MSFMoveNext = MSFlexGrid1
i = MSF1
GoTo MoveNext
ElseIf iSellNum = 0 Then
Set MSFMoveNext = MSFlexGrid2
i = MSF2
GoTo MoveNext
End If

Loop

Set MSFMoveNext = Nothing

End Sub

******** end code

Just cut the old one and paste this new one.

Thanks

Wileecoy.
0

Author Comment

ID: 6472626
Thanks wileecoy for all your help...
0

Author Comment

ID: 6472627
Thanks wileecoy for all your help...It works perfectly...

0

Author Comment

ID: 6472640
Thanks wileecoy for all your help...It works perfectly...
0

LVL 4

Expert Comment

ID: 6472661

Great thinker question.

Let me know if you have anymore!

Wileecoy.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.