Slow VBA module in small Access DB

[Loot]

Hello, all -

I have an Access DB I am designing. I am still in development, so the
DB is empty - only has 6 records in it right now. Now, this DB is for
a trading firm and I have a very specfic requirement on how prices of
trades are displayed. So, I wrote a VBA funtion to take the actual
price from the DB and return a formatted string (function code below).

My problem is that I am calling this function from a continuous
subform so all of the displayed records will display the price
correctly. I have a text box I am using to display the price, and the
control source is set to =displayPrice(price)

This works perfectly, but there is a noticable lag - perhaps a second
- between when the form opens and the prices are displayed. This has
me nervous, since if it is lagging with 6 records, what will happen
when it is 100? Can anyone recommend a way to speed this up, or is
there something I'm missing in the below function which would cause it
to process slowly?

Thanks for any help - definitely not a VBA pro, just someone with some
experience who has been thrown into the mix by his job...

Public Function displayPrice(decimalPrice As String) As String
'This function lets us take any price and display in ticks or decimal
as appropriate
Dim priceArray As Variant
Dim priceInTicks As String

'Insert a test to see if decimalPrice comes back as a whole number,
like if a user enters
'100-32 (it will pass 101). If so, return the decimal.

If int(decimalPrice) = decimalPrice Then
displayPrice = decimalPrice
Exit Function
End If

'Otherwise, we are on our way
priceArray = Split(decimalPrice, ".")
priceArray(1) = (("." & priceArray(1)) * 32)

'After tick conversion, we want to convert ".5" to "+", but only if
the tick ends in .5
'In other words, we don't want 100-31.5253 to show as 100-31+253
'Those wacky prices are rare, but we can still handle them

'If the tick ends in .25, .5, or .75 we display it - anything else, we
exit and return the decimal. I can't see
'the need in displaying some oddball tick number like 101-03.27462 -
decimal is better in that
'scenario

If InStr(priceArray(1), ".") Then
Dim ticksArray As Variant
ticksArray = Split(priceArray(1), ".")
'Test to make sure the decimal portion is one of our accepted
values
If ticksArray(1) <> "25" And ticksArray(1) <> "5" And ticksArray
(1) <> "75" Then
displayPrice = decimalPrice
Exit Function
End If

'Adds leading zero to single digits for aesthetics
If ticksArray(0) < 10 Then
ticksArray(0) = "0" & ticksArray(0)
End If

'Test the decimal portion to see if it is equal to ".5"
If ticksArray(1) = "5" Then
ticksArray(1) = "+"
'Now, replace priceArray(1) with our result
priceArray(1) = ticksArray(0) & ticksArray(1)
Else
priceArray(1) = ticksArray(0) & "." & ticksArray(1)
End If
Else
'Adds leading zero to single digits for aesthetics
If priceArray(1) < 10 Then
priceArray(1) = "0" & priceArray(1)
End If
End If

'Form our string and return it
displayPrice = priceArray(0) & "-" & priceArray(1)
End Function

 

[Dirk Goldgar]

Hello, all -

I have an Access DB I am designing. I am still in development, so the
DB is empty - only has 6 records in it right now. Now, this DB is for
a trading firm and I have a very specfic requirement on how prices of
trades are displayed. So, I wrote a VBA funtion to take the actual
price from the DB and return a formatted string (function code below).

My problem is that I am calling this function from a continuous
subform so all of the displayed records will display the price
correctly. I have a text box I am using to display the price, and the
control source is set to =displayPrice(price)

This works perfectly, but there is a noticable lag - perhaps a second
- between when the form opens and the prices are displayed. This has
me nervous, since if it is lagging with 6 records, what will happen
when it is 100? Can anyone recommend a way to speed this up, or is
there something I'm missing in the below function which would cause it
to process slowly?

Thanks for any help - definitely not a VBA pro, just someone with some
experience who has been thrown into the mix by his job...

Public Function displayPrice(decimalPrice As String) As String
'This function lets us take any price and display in ticks or decimal
as appropriate
Dim priceArray As Variant
Dim priceInTicks As String

'Insert a test to see if decimalPrice comes back as a whole number,
like if a user enters
'100-32 (it will pass 101). If so, return the decimal.

If int(decimalPrice) = decimalPrice Then
displayPrice = decimalPrice
Exit Function
End If

'Otherwise, we are on our way
priceArray = Split(decimalPrice, ".")
priceArray(1) = (("." & priceArray(1)) * 32)

'After tick conversion, we want to convert ".5" to "+", but only if
the tick ends in .5
'In other words, we don't want 100-31.5253 to show as 100-31+253
'Those wacky prices are rare, but we can still handle them

'If the tick ends in .25, .5, or .75 we display it - anything else, we
exit and return the decimal. I can't see
'the need in displaying some oddball tick number like 101-03.27462 -
decimal is better in that
'scenario

If InStr(priceArray(1), ".") Then
Dim ticksArray As Variant
ticksArray = Split(priceArray(1), ".")
'Test to make sure the decimal portion is one of our accepted
values
If ticksArray(1) <> "25" And ticksArray(1) <> "5" And ticksArray
(1) <> "75" Then
displayPrice = decimalPrice
Exit Function
End If

'Adds leading zero to single digits for aesthetics
If ticksArray(0) < 10 Then
ticksArray(0) = "0" & ticksArray(0)
End If

'Test the decimal portion to see if it is equal to ".5"
If ticksArray(1) = "5" Then
ticksArray(1) = "+"
'Now, replace priceArray(1) with our result
priceArray(1) = ticksArray(0) & ticksArray(1)
Else
priceArray(1) = ticksArray(0) & "." & ticksArray(1)
End If
Else
'Adds leading zero to single digits for aesthetics
If priceArray(1) < 10 Then
priceArray(1) = "0" & priceArray(1)
End If
End If

'Form our string and return it
displayPrice = priceArray(0) & "-" & priceArray(1)
End Function

I don't understand the nature of the prices you are dealing with, but I can
see one way to speed this up. You are constantly using subscripting to get
to the same elements of the arrays -- priceArray(0), priceArray(1),
ticksArray(0), ticksArray(1). I think it would be quicker to pull these
elements out of the arrays and work with them as simple String variables.
Here's an untested version of the function modified according to that idea:

'------ start of modified code ------
Public Function displayPrice(decimalPrice As String) As String

' This function lets us take any price and display
' in ticks or decimal as appropriate

Dim priceArray As Variant
Dim priceInTicks As String
Dim Price1 As String, Price2 As String

' Insert a test to see if decimalPrice comes back as
' a whole number, like if a user enters
' 100-32 (it will pass 101). If so, return the decimal.

If Int(decimalPrice) = decimalPrice Then
displayPrice = decimalPrice
Exit Function
End If

'Otherwise, we are on our way
priceArray = Split(decimalPrice, ".")
Price1 = priceArray(0)
Price2 = priceArray(1)

Price2 = (("." & Price2) * 32)

' After tick conversion, we want to convert ".5" to "+",
' but only if the tick ends in .5
' In other words, we don't want 100-31.5253 to show
' as 100-31+253
' Those wacky prices are rare, but we can still handle them

' If the tick ends in .25, .5, or .75 we display it -
' anything else, we exit and return the decimal.
' I can't see the need in displaying some oddball
' tick number like 101-03.27462 - decimal is better in
' that scenario

If InStr(Price2, ".") Then
Dim ticksArray As Variant
Dim Ticks1 As String, Ticks2 As String

ticksArray = Split(Price2, ".")
Ticks1 = ticksArray(0)
Ticks2 = ticksArray(1)

' Test to make sure the decimal portion is
' one of our accepted values
Select Case Ticks2
Case "25", "5", "75"
' Do nothing; continue processing
Case Else
displayPrice = decimalPrice
Exit Function
End Select

'Adds leading zero to single digits for aesthetics
If Ticks1 < 10 Then
Ticks1 = "0" & Ticks1
End If

'Test the decimal portion to see if it is equal to ".5"
If Ticks2 = "5" Then
Ticks2 = "+"
'Now, replace Price2 with our result
Price2 = Ticks1 & Ticks2
Else
Price2 = Ticks1 & "." & Ticks2
End If
Else
'Adds leading zero to single digits for aesthetics
If Price2 < 10 Then
Price2 = "0" & Price2
End If
End If

'Form our string and return it
displayPrice = Price1 & "-" & Price2

End Function
'------ end of modified code ------

I suspect there are other ways to improve the efficiency, but since I don't
understand the nature and possible values of your trading prices, I'm not
going to charge ahead with them.

Normally, by the way, when displaying function results in a continuous form
or datasheet, Access tries not to calculate the values until the row is
actually going to be displayed. So even if the function is slow, you may
not find that the delay scales up with the number of records. It depends on
whether the calculated values are need for anything before the records are
displayed.

 

[Loot]

Dirk -

Thank you for the reply - I just implemented and tested the string
solution. It doesn't display the prices any faster, but I do like that
method better than continuously using the subscripting. A "why didn't
I think of that" moment.

I'll try and explain the pricing in the hopes you can offer some
additional feedback.

These are bond prices, which are quoted as a percent of the original
(par) value of the bond. So, if the bond certificate is issued at
$1,000 value and is now priced at 99, it would be $1,000 * 99% = $990

Now, these bonds don't only trade in whole number prices - they trade
in "ticks", or 1/32's of a percent. So, a price of 99-05 is "ninety
nine and five ticks" or 99.(5/32)% (99.15625)

To complicate matters more, they can trade in half-ticks as well,
called a "plus" and designated by a plus symbol. So, 99-05+ is "ninety-
nine five plus" or 99.(5.5/32) (99.171875). In a more rare situation,
bonds can be priced in quarter ticks or three quarter ticks - no fancy
name for them there, they would be displayed as 99-05.25. Finally,
every once in awhile, there is some oddball price such as 99-5.27646 -
in this instance, I'd rather display the price in decimal
(99.164889375) than a weird tick amount.

I'd love to throttle whoever came up with this convoluted
system.....but anyway -

Obviously, I store all prices in their decimal form on the backend,
but for readability and to appease the traders, I want to display them
in tick format when they are viewing the trades in my form. So, my
function does the following -

-tests if the decimal number is a whole number - sometimes a user will
enter something like 101-32 which is equivalent to 102 (it is
101+32/32's) - in this case, it is stored in the DB as 102 and should
be displayed as such

-if it is not a whole number and has a decimal portion, we split it at
the decimal point so we can manipulate the decimal portion
individually. Adding a decimal point back to the priceArray(1) element
and multiplying it by 32 would get us the tick value (so DB holds
101.25, gets spilt into 101 and 25, we take the 25 and make it .25 *
32 = 8 ticks)

-now, if it is appropriate to add a + (for instance, tick value comes
back as 8.5 and should be displayed as 8+) we do so. So we check to
see if the tick value contains a decimal. Also, while we are here, we
check to make sure the decimal portion is .25, .5, or .75 - if not, it
is one of those oddball prices we mentioned earlier and we choose to
just return the decimal format instead of ticks.

-finally, if the ticks portion is less than 10, we add a leading zero
- traders would rather see 101-08 than 101-8

That's about it - very convoluted, I know, and I hope I haven't lost
you. If you can think of a way to optimize this or handle it in
another way, I'd love to hear it. This was my first crack at it, and
perhaps I am not aware of other VB/Access functions which could
replace some of this code.

Thanks so much for the time -

Jim

 

[Dirk Goldgar]

Dirk -

Thank you for the reply - I just implemented and tested the string
solution. It doesn't display the prices any faster, but I do like that
method better than continuously using the subscripting. A "why didn't
I think of that" moment.

I'll try and explain the pricing in the hopes you can offer some
additional feedback.

These are bond prices [...]

(Long explanation snipped. But I did read it. <g>)

Are the prices stored in the database as strings, or a numeric values (maybe
double-precision float, maybe decimal fields)? You say, "I store all prices
in their decimal form", but I don't know if that means you actually store
them as numbers, or as string representations of numbers. I see that your
function is set up to take a string argument.