wegwong
asked on
Basic logic
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.
When a sell prices matches a buy price the stock trades.
Buyers
Level Buy Quantity Price
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
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.
When a sell prices matches a buy price the stock trades.
Buyers
Level Buy Quantity Price
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
ASKER
Hello Mr Kamal,
thanks for your comments.
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
thanks for your comments.
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
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
private form_load()
dim wsheet as worksheet
dim wbook as workbook
set appexcel=createobject("Exc
set wbook=getobject("file name in excel")
set wsheet=wbook.sheets(sheet no)
wshhet.cells.value()
and to calculate anything in excel:
appexcel.evaluate(expressi
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
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
private form_load()
dim wsheet as worksheet
dim wbook as workbook
set appexcel=createobject("Exc
set wbook=getobject("file name in excel")
set wsheet=wbook.sheets(sheet no)
wshhet.cells.value()
and to calculate anything in excel:
appexcel.evaluate(expressi
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
Basic Logic.... hehehe.
This is a good one.
However, please explain "priority buy price" and "priority sell price".
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.
This is a good one.
However, please explain "priority buy price" and "priority sell price".
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.
Ok - what happens when there is no priority overlap, but when latter trades would overlap?
For example:
A. Buy 100 @ 240
B. Buy 300 @ 242
C. Buy 500 @ 243
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.
In this case, If you start with 'Buy A' and 'Sell A' (as priority trades), There is no overlap as the buy is below the sell.
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
For example:
A. Buy 100 @ 240
B. Buy 300 @ 242
C. Buy 500 @ 243
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.
In this case, If you start with 'Buy A' and 'Sell A' (as priority trades), There is no overlap as the buy is below the sell.
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
ASKER
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
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
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
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
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.
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.
ASKER
Thanks Wileecoy..
ASKER
Thanks Wileecoy..
ASKER
Thanks Wileecoy..
ASKER
oops, don't know what happened, sorry.
'*************************
'* Norm Floria *'
'* September 2001 *'
'* Calculates Market Open Price for a stock *'
'* *'
'* *'
'* See the following link for more information on the *'
'* 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 iBuy As Integer
Dim iSell As Integer
Dim dOpenPrice As Double
Private Sub Command1_Click()
Unload Me
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
If TranType = "Buy" Then
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
If Combo1.Text = "Buy" Then 'this means that we are buying
iBuy = GetRow(MSFlexGrid1, Text2.Text, "Buy")
MSFlexGrid1.AddItem "B" & iBuy & vbTab & Text1.Text & vbTab & Text2.Text, iBuy
ReDoPriority MSFlexGrid1, iBuy, "Buy"
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
If TransType = "Buy" Then
msf.Text = "B" & i
Else
msf.Text = "S" & i
End If
Next
End Sub
Private Function Is_There_Overlap() As Boolean
Dim dBuy As Double
Dim dSell As Double
dBuy = Val(MSFlexGrid1.TextMatrix
dSell = Val(MSFlexGrid2.TextMatrix
If dBuy > dSell Or dBuy = dSell Then
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
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 iBuyOrderQty As Integer
Dim dBuyOrderPrice As Double
Dim iSellOrderQty As Integer
Dim dSellOrderPrice As Double
'To Store Order Price
Dim dOpenPrice As Double
'Variables For Buy Side
Dim sBuyP As String 'Priority
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
sBuyP = .TextMatrix(i, 0) 'This is the Buy/Sell Code
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
If dBuyPrice > dSellPrice Or dBuyPrice = dSellPrice Then
'Here we store the final Order
iBuyOrderQty = iBuyNum
dBuyOrderPrice = dBuyPrice
iSellOrderQty = iSellNum
dSellOrderPrice = dSellPrice
'Now we need to keep offsetting quantities until there is no overlap
If iBuyNum > iSellNum Or iBuyNum = iSellNum Then
iBuyNum = iBuyNum - iSellNum
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
If iBuyNum = 0 Then
MSF1 = MSF1 + 1
End If
Else
iSellNum = iSellNum - iBuyNum
iBuyNum = 0
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
dOpenPrice = ((iBuyOrderQty * dBuyOrderPrice) + (iSellOrderQty * dSellOrderPrice)) / (iBuyOrderQty + iSellOrderQty)
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
If iBuyNum = 0 Then
sBuyP = .TextMatrix(MSF1, 0) 'This is the Buy/Sell Code
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
If iBuyNum = 0 Then
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
Private Sub Form_Load()
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.AddItem "Buy"
Combo1.AddItem "Sell"
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
.AddItem Join(varArray, vbTab)
.AddItem " "
.FixedRows = 1
.Row = 0
.Col = 0
End With
With MSFlexGrid2
.Clear
.AllowBigSelection = False
.Rows = 0
.Cols = UBound(varArray) + 1
.AddItem Join(varArray, vbTab)
.AddItem " "
.FixedRows = 1
.Row = 0
.Col = 0
End With
PopulateBuySell
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 PopulateBuySell()
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
'* Norm Floria *'
'* September 2001 *'
'* Calculates Market Open Price for a stock *'
'* *'
'* *'
'* See the following link for more information on the *'
'* 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 iBuy As Integer
Dim iSell As Integer
Dim dOpenPrice As Double
Private Sub Command1_Click()
Unload Me
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
If TranType = "Buy" Then
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
If Combo1.Text = "Buy" Then 'this means that we are buying
iBuy = GetRow(MSFlexGrid1, Text2.Text, "Buy")
MSFlexGrid1.AddItem "B" & iBuy & vbTab & Text1.Text & vbTab & Text2.Text, iBuy
ReDoPriority MSFlexGrid1, iBuy, "Buy"
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
If TransType = "Buy" Then
msf.Text = "B" & i
Else
msf.Text = "S" & i
End If
Next
End Sub
Private Function Is_There_Overlap() As Boolean
Dim dBuy As Double
Dim dSell As Double
dBuy = Val(MSFlexGrid1.TextMatrix
dSell = Val(MSFlexGrid2.TextMatrix
If dBuy > dSell Or dBuy = dSell Then
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
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 iBuyOrderQty As Integer
Dim dBuyOrderPrice As Double
Dim iSellOrderQty As Integer
Dim dSellOrderPrice As Double
'To Store Order Price
Dim dOpenPrice As Double
'Variables For Buy Side
Dim sBuyP As String 'Priority
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
sBuyP = .TextMatrix(i, 0) 'This is the Buy/Sell Code
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
If dBuyPrice > dSellPrice Or dBuyPrice = dSellPrice Then
'Here we store the final Order
iBuyOrderQty = iBuyNum
dBuyOrderPrice = dBuyPrice
iSellOrderQty = iSellNum
dSellOrderPrice = dSellPrice
'Now we need to keep offsetting quantities until there is no overlap
If iBuyNum > iSellNum Or iBuyNum = iSellNum Then
iBuyNum = iBuyNum - iSellNum
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
If iBuyNum = 0 Then
MSF1 = MSF1 + 1
End If
Else
iSellNum = iSellNum - iBuyNum
iBuyNum = 0
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
dOpenPrice = ((iBuyOrderQty * dBuyOrderPrice) + (iSellOrderQty * dSellOrderPrice)) / (iBuyOrderQty + iSellOrderQty)
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
If iBuyNum = 0 Then
sBuyP = .TextMatrix(MSF1, 0) 'This is the Buy/Sell Code
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
If iBuyNum = 0 Then
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
Private Sub Form_Load()
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.AddItem "Buy"
Combo1.AddItem "Sell"
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
.AddItem Join(varArray, vbTab)
.AddItem " "
.FixedRows = 1
.Row = 0
.Col = 0
End With
With MSFlexGrid2
.Clear
.AllowBigSelection = False
.Rows = 0
.Cols = UBound(varArray) + 1
.AddItem Join(varArray, vbTab)
.AddItem " "
.FixedRows = 1
.Row = 0
.Col = 0
End With
PopulateBuySell
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 PopulateBuySell()
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
The instructions are in the header text of the form.
Just paste all of that into Form1 of a standard project.
Add the MSFlexGrid Component.
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.
Just paste all of that into Form1 of a standard project.
Add the MSFlexGrid Component.
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.
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.
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.
ASKER
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.
pain but would u mind sending the project file to
me a wegwong@hotmail.com
I give it a go.
Thanks.
ASKER
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.
pain but would u mind sending the project file to
me a wegwong@hotmail.com
I give it a go.
Thanks.
Ok - please hold.
ASKER
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.
pain but would u mind sending the project file to
me a wegwong@hotmail.com
I give it a go.
Thanks.
Done.
ASKER
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'
'PopulateBuySell
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
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'
'PopulateBuySell
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
ASKER
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'
'PopulateBuySell
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
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'
'PopulateBuySell
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
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.
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.
ASKER
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.
"
ASKER
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
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
OK - Let me put that in.
Thanks.
Wileecoy.
Thanks.
Wileecoy.
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
If iBuyOrderQty > iSellOrderQty Then
iMatch = iBuyOrderQty - iSellOrderQty
sFavor = ", Buy over Sell."
Else
iMatch = iSellOrderQty - iBuyOrderQty
sFavor = ", Sell over Buy."
End If
dOpenPrice = ((iBuyOrderQty * dBuyOrderPrice) + (iSellOrderQty * dSellOrderPrice)) / (iBuyOrderQty + iSellOrderQty)
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!
'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
If iBuyOrderQty > iSellOrderQty Then
iMatch = iBuyOrderQty - iSellOrderQty
sFavor = ", Buy over Sell."
Else
iMatch = iSellOrderQty - iBuyOrderQty
sFavor = ", Sell over Buy."
End If
dOpenPrice = ((iBuyOrderQty * dBuyOrderPrice) + (iSellOrderQty * dSellOrderPrice)) / (iBuyOrderQty + iSellOrderQty)
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!
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.
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.
ASKER
ASKER
Test, EE failed to post last message.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks wileecoy for all your help...
ASKER
Thanks wileecoy for all your help...It works perfectly...
ASKER
Thanks wileecoy for all your help...It works perfectly...
Glad to help wegwong.
Great thinker question.
Let me know if you have anymore!
Wileecoy.
Great thinker question.
Let me know if you have anymore!
Wileecoy.
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