Solved

Basic logic

Posted on 2001-09-02
36
198 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:wegwong
  • 19
  • 15
  • 2
36 Comments
 

Expert Comment

by:kamaldeep
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

by:wegwong
ID: 6449664
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
0
 

Expert Comment

by:kamaldeep
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
private form_load()
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

by:wileecoy
ID: 6451413
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.
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6451699
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
0
 

Author Comment

by:wegwong
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

by:wileecoy
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

by:wileecoy
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

by:wegwong
ID: 6452076
Thanks Wileecoy..

0
 

Author Comment

by:wegwong
ID: 6452078
Thanks Wileecoy..

0
 

Author Comment

by:wegwong
ID: 6452085
Thanks Wileecoy..

0
 

Author Comment

by:wegwong
ID: 6452088
oops, don't know what happened, sorry.
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6452105
'************************************************************'
'* 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(1, 2))
dSell = Val(MSFlexGrid2.TextMatrix(1, 2))

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(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 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
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6452113
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.
0
 
LVL 4

Expert Comment

by:wileecoy
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

by:wegwong
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

by:wegwong
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

by:wileecoy
ID: 6452153
Ok - please hold.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:wegwong
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

by:wileecoy
ID: 6452160
Done.
0
 

Author Comment

by:wegwong
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'

'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



0
 

Author Comment

by:wegwong
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'

'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



0
 
LVL 4

Expert Comment

by:wileecoy
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

by:wegwong
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

by:wegwong
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

by:wileecoy
ID: 6454884
OK - Let me put that in.

Thanks.

Wileecoy.
0
 
LVL 4

Expert Comment

by:wileecoy
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
        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!
0
 
LVL 4

Expert Comment

by:wileecoy
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

by:wegwong
ID: 6455269
0
 

Author Comment

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

Author Comment

by:wegwong
ID: 6455302
0
 
LVL 4

Accepted Solution

by:
wileecoy earned 300 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 iBuyOrderQty As Long
Dim dBuyOrderPrice As Double
Dim iSellOrderQty As Long
Dim dSellOrderPrice As Double

'To Store Order Price
Dim dOpenPrice As Double

'To Store Match Volume Accumulators
Dim iBuyTotal As Long
Dim iSellTotal As Long

'Variables For Buy Side
Dim sBuyP As String        'Priority
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
    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

iBuyTotal = iBuyNum
iSellTotal = iSellNum


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
        Dim iMatch As Long
        'Dim sFavor As String
        If iSellTotal > iBuyTotal Then
            iMatch = iBuyTotal - (iBuyOrderQty - iSellOrderQty)
            'sFavor = ", Buy over Sell."
        Else
            iMatch = iSellTotal - (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 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
            iBuyTotal = iBuyTotal + iBuyNum
        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
   
    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


******** end code

Just cut the old one and paste this new one.

Thanks

Wileecoy.
0
 

Author Comment

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

Author Comment

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

0
 

Author Comment

by:wegwong
ID: 6472640
Thanks wileecoy for all your help...It works perfectly...
0
 
LVL 4

Expert Comment

by:wileecoy
ID: 6472661
Glad to help wegwong.

Great thinker question.

Let me know if you have anymore!

Wileecoy.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now