Pause until MSNSTockQuote runs

[Ron Rosenfeld]

As part of a macro, I am modifying several worksheets and the modification
includes adding a column of about 30 rows with the function of the form:

=MSNStockQuote($A6,"Last","US")

This command fetches data from the Internet.
After it runs, other cells are calculated.

I would like to know when the function has obtained the data, in all of the
rows, so that I can then "autosize" the columns. (If I don't wait, the columns
will be too small).

I know I can do something like a while/wend loop, but I don't know what to use
as a flag for this routine.

I could, for example, use the presence of real data in the bottommost row, but
if that happens to be a quote for which there happens to be no data; or if the
server is down; or if the macro is being run during the "5-minute" timeout of
the function, then my macro will "hang".

Any thoughts would be appreciated.

Thanks.
--ron

 

[Don Guillett]

As always, show all of your code for suggestions

 

[Ron Rosenfeld]

As always, show all of your code for suggestions

Don,

I was hoping there would be a generic answer to my question, so didn't. But
below is the code for that macro.

The workbook containing the relevant worksheets (of which three are manually
selected), contains about seven worksheets. The relevant worksheets are the
results of some stock screening software. This macro formats the data so it is
more legible, and adds a few columns to perform certain calculations on the
results of the screened data. The end result is a value which is the number of
shares to purchase in order to bring the position to a predetermined value.

The three worksheets are identified, within this macro, by the name containing
either Value, Growth or Tiny, and a different position size constant is used
depending on which sheet it is.

And the format of the three worksheets is not the same (different numbers of
columns), so the extra columns go in a different location depending on which
sheet we are dealing with.

(This is something I've done manually for years; now trying to automate more of
the procedure).

===============================
Option Explicit
Option Compare Text
Sub SetUpSheets()
Dim c As Range
Dim CurPosns As Range
Dim sWSName As String
Dim lNumStocks As Long
Dim sPosnSize As String
Dim ws As Worksheet

For Each ws In Application.ActiveWindow.SelectedSheets

ws.Activate
sWSName = ActiveSheet.Name
lNumStocks = Application.WorksheetFunction.CountA(Range("A:A")) - 1
GetCurrPosns CurPosns

If CurPosns Is Nothing Then
MsgBox ("Need to Copy MSMoney Portfolio Report" & _
vbLf & " Sorted by Position" & vbLf & _
" to Positions Worksheet")
Exit Sub
End If

Worksheets(sWSName).Activate
If Range("A1") <> "Ticker" Then
MsgBox ("Not a Valid Worksheet")
Exit Sub
End If

If InStr(1, sWSName, "Tiny", vbTextCompare) > 0 Then
sPosnSize = "PosnSizeTT"
ElseIf InStr(1, sWSName, "Value", vbTextCompare) > 0 Then
sPosnSize = "PosnSizeValue"
ElseIf InStr(1, sWSName, "Growth", vbTextCompare) > 0 Then
sPosnSize = "PosnSizeGrowth"
Else
MsgBox ("Not a Valid Worksheet")
Exit Sub
End If

Application.ScreenUpdating = False

Rows("1:4").Insert Shift:=xlDown

Set c = Range("A5").End(xlToRight)
c.Offset(-1, 1).FormulaR1C1 = "Current"
c.Offset(0, 1).FormulaR1C1 = "Price"
c.Offset(-1, 2).FormulaR1C1 = "Current"
c.Offset(0, 2).FormulaR1C1 = "Holdings"
c.Offset(-1, 3).FormulaR1C1 = "Shares to"
c.Offset(0, 3).FormulaR1C1 = "Purchase"
c.Offset(0, 4).FormulaR1C1 = "Amount"

With Range("A5").CurrentRegion
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISODD(ROW())"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.799981688894314
End With
.FormatConditions(1).StopIfTrue = False
End With

c.Offset(1, 1).FormulaR1C1 = "=MSNStockQuote(RC1,""Last"",""US"")"
c.Offset(1, 1).Style = "Currency"

c.Offset(1, 2).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1," &
CurPosns.Worksheet.Name _
& "!" & CurPosns.Address(True, True, xlR1C1) & ",5,FALSE),0)"
c.Offset(1, 2).NumberFormat = "0;0;;"

c.Offset(1, 3).FormulaR1C1 = "=INT(" & sPosnSize & "/RC[-2])-RC[-1]"
c.Offset(1, 3).NumberFormat = "#,##0_);[Red](#,##0)"

c.Offset(1, 4).FormulaR1C1 = "=RC[-1]*RC[-3]+8"
c.Offset(1, 4).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Set c = c.Offset(1, 1)

Set c = c.Resize(lNumStocks, 4)
c.FillDown
Set c = c.Offset(-2).Resize(lNumStocks + 2)
c.Columns.AutoFit

Application.ScreenUpdating = True

Next ws

End Sub
===============================
--ron

 

[Peter T]

Hi Ron,

I know I can do something like a while/wend loop, but I don't know what to
use
as a flag for this routine.

In a light test the following seemed to work for me.

Sub test()
' requires the free MSNStockQuote addin
Dim bGot As Boolean
Dim t As Single
Dim timeOut As Single
Dim aa, bb

timeOut = 3 ' seconds
t = Timer
With Range("A1")
.ClearContents
.FormulaR1C1 = "=MSNStockQuote(""msft"",""Last"",""US"")"
aa = .Value
If IsError(aa) Then aa = .Text
bGot = False
Do
bb = .Value
If VarType(bb) <> vbError Then
If Len(bb) Then bGot = IsNumeric(bb)
End If
Loop Until bGot Or (Timer - t > timeOut)

If IsError(bb) Then bb = .Text & " " & CStr(bb) ' eg #N/A or #VALUE
End With

MsgBox "before-loop: " & aa & vbCr & _
"after - loop : " & bb
End Sub


I notice after a (very) long time MSNStockQuote shows its own timed-out msg
if say a non valid ticker is used.

For more control perhaps try Web-Query

Regards,
Peter T

 

[Ron Rosenfeld]

Hi Ron,


In a light test the following seemed to work for me.

Sub test()
' requires the free MSNStockQuote addin
Dim bGot As Boolean
Dim t As Single
Dim timeOut As Single
Dim aa, bb

timeOut = 3 ' seconds
t = Timer
With Range("A1")
.ClearContents
.FormulaR1C1 = "=MSNStockQuote(""msft"",""Last"",""US"")"
aa = .Value
If IsError(aa) Then aa = .Text
bGot = False
Do
bb = .Value
If VarType(bb) <> vbError Then
If Len(bb) Then bGot = IsNumeric(bb)
End If
Loop Until bGot Or (Timer - t > timeOut)

If IsError(bb) Then bb = .Text & " " & CStr(bb) ' eg #N/A or #VALUE
End With

MsgBox "before-loop: " & aa & vbCr & _
"after - loop : " & bb
End Sub


I notice after a (very) long time MSNStockQuote shows its own timed-out msg
if say a non valid ticker is used.

For more control perhaps try Web-Query

Regards,
Peter T

Thanks. I'll give that idea a try. I appreciate that.

I suppose I could just check all the cells until data has been returned, and
perhaps set a maximum time. I was hoping there might be some value that might
get returned after MSNStockQuote has finished running.
--ron

 

[Don Guillett]

Ron,
As a retired stock broker this is something I do often. If desired, send
your workbook to me along with desires and I will see if I can make your
life a bit easier.

 

[Ron Rosenfeld]

Ron,
As a retired stock broker this is something I do often. If desired, send
your workbook to me along with desires and I will see if I can make your
life a bit easier.

Don,

Thanks for the offer. But I've got everything working to my liking, except for
having to wait until MSNStockQuote updates the quotes before I can auto-size my
sheet.

And that is only happening the first time through. (It's something I can live
with for now).

I think it would be difficult for me to explain things in words. As an
exercise, I will try.

The methodology I use (in general), consists of generating a buy list using
three separate screens from AAII Stock Investor Pro.

The number of positions to be purchased from each screen is a fixed number. And
the position size ($) is determined by the particular screen (two of them will
comprise 45% of my holdings, and the third 10%).

The actual position size is computed on information imported from Microsoft
Money. It depends on the funds available in my investment accounts, which will
include equity positions that can be closed, as well as cash; and also
determined by the amount of CASH I wish to have after making these investments.

Then I need to check to see if I already have positions in the stocks on the
buy list, so purchase/sale can be done to bring that to the determined position
size.

Finally, the number of stocks to be purchased/sold is computed based on the
desired position size, current price, and current holdings.

I do this once a year. And the total number of positions is 75, so computer
assistance is pretty helpful.
--ron

 

[Don Guillett]

Then, without suggesting other changes to your code, and without seeing your
wb,
IF?? I understand your original question, try this.c.EntireColumn.AutoFit

 

[Ron Rosenfeld]

Then, without suggesting other changes to your code, and without seeing your
wb,
IF?? I understand your original question, try this.
c.EntireColumn.AutoFit

I'll try that, although I have similar code already:

c.Columns.AutoFit

Hmmm -- I'll also check to be sure that c is referring to the range I think it
is.


The problem is that the .AutoFit executes prior to the stock quotes having been
downloaded, given the method I am using (writing the formula into the cell). So
that column does not get properly sized.

Ah well. For now I'll live with it. In the future I may have to go to another
method of downloading the quotes, that exists within VBA.
--ron

 

[Don Guillett]

As I mentioned, if desired send me yours and tell me what you are trying to
do. I use yahoo quotes and get data for as many as desired with one mouse
click. Send me your email and I'll send you a sample.