MSN Stock Quote Add In VBA Declare and Call

[Newq]

Does anyone know how to Declare and Call the dll to update stock quotes
combined with a timer so that quotes are automatically updated after the close

 

[GTVT06]

Does anyone know how to Declare and Call the dll to update stock quotes
combined with a timer so that quotes are automatically updated after the close

I'm not sure which part of MSN your wanting exactly, but here's a VBA
code I put together for you that will grab some US stocks and refresh
them every 15mins or every time you run the macro.

Sub Stocks()
Dim n As Single
Dim exist As Boolean
For n = 1 To Sheets.Count
Sheets(n).Activate
If ActiveSheet.Name = "Stocks" Then
exist = True
End If
Next n
If exist = False Then
Sheets.Add.Name = "Stocks"
End If
Worksheets("Stocks").Activate
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://moneycentral.msn.com/investor/market/
usindex.aspx", Destination:= _
Range("A1"))
.Name = "usindex"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 15
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "6,7"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Newq]

I'm not sure which part of MSN your wanting exactly, but here's a VBA
code I put together for you that will grab some US stocks and refresh
them every 15mins or every time you run the macro.

Sub Stocks()
Dim n As Single
Dim exist As Boolean
For n = 1 To Sheets.Count
Sheets(n).Activate
If ActiveSheet.Name = "Stocks" Then
exist = True
End If
Next n
If exist = False Then
Sheets.Add.Name = "Stocks"
End If
Worksheets("Stocks").Activate
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://moneycentral.msn.com/investor/market/
usindex.aspx", Destination:= _
Range("A1"))
.Name = "usindex"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 15
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "6,7"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Thank you for your help. I will incorporate it into what i am dong and may
be back to you with another question. Thanks again for your fast response

Newq

 

[GTVT06]

Thank you for your help.  I will incorporate it into what i am dong and may
be back to you with another question.  Thanks again for your fast response

Newq- Hide quoted text -

- Show quoted text -

No problem

 

[Newq]

No problem

I have tried to run your macro and cannot get VBA to accept the "With Active
Sheet" code. It looks like the URL is not accepted. Obviously when I try to
go to this URL I get a 402 error saying that while the URL is recognized it
cannot be opened which makes sense. What am I missing?

I believe I see what your code is doing. To clarify what i am doing I have
a single worksheet with a column of stocks and indices and am updating date,
last day and daily hi, low and Last prices. in subsequent columns. It is my
intention to run the macro once per day after the close of markets to get day
end closing prices, high and low.

Therefore, can you help me with the URL call and how would you modify the
code to accomplish my more precise objective?

NEWQ

 

[Newq]

I have tried to run your macro and cannot get VBA to accept the "With Active
Sheet" code. It looks like the URL is not accepted. Obviously when I try to
go to this URL I get a 402 error saying that while the URL is recognized it
cannot be opened which makes sense. What am I missing?

I believe I see what your code is doing. To clarify what i am doing I have
a single worksheet with a column of stocks and indices and am updating date,
last day and daily hi, low and Last prices. in subsequent columns. It is my
intention to run the macro once per day after the close of markets to get day
end closing prices, high and low.

Therefore, can you help me with the URL call and how would you modify the
code to accomplish my more precise objective?

NEWQ

Since I wrete the above I have succeeded in getting your macro to work. I
had copied out your macro and in pasting it in from a word document I had a
spacing issue. Now that I see the output I have two questions.

First, how do I contorl the stocks, indices or symbols that the macro grabs?


Second, Iwould like to grab the following data for each:
Date of Last Sale
Day's High
Day' Low
Last Price
Open Price
Change
% Change

in that order.

Many thnks in advance for your input.

Newq