ExcelBanter - Pause until MSNSTockQuote runs

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Pause until MSNSTockQuote runs (https://www.excelbanter.com/excel-programming/421733-pause-until-msnstockquote-runs.html)

Pause until MSNSTockQuote runs

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

Pause until MSNSTockQuote runs

On Fri, 26 Dec 2008 07:47:15 -0600, "Don Guillett"
wrote:

As always, show all of your code for suggestions

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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).SetFirs tPriority
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

Pause until MSNSTockQuote runs

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" wrote in message
...
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

Pause until MSNSTockQuote runs

On Sat, 27 Dec 2008 11:52:36 -0000, "Peter T" <peter_t@discussions wrote:

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

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

Pause until MSNSTockQuote runs

On Sun, 28 Dec 2008 08:12:39 -0600, "Don Guillett"
wrote:

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 Guillett
Microsoft MVP Excel
SalesAid Software

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

Pause until MSNSTockQuote runs

On Sun, 28 Dec 2008 10:38:37 -0600, "Don Guillett"
wrote:

Then, without suggesting other changes to your code, and without seeing your
wb,
IF?? I understand your original question, try this.
c.Columns.autofit
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

Pause until MSNSTockQuote runs