Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSN Stock Quote Add In VBA Declare and Call
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSN Stock Quote Add In VBA Declare and Call
On Apr 3, 1:02*pm, Newq wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSN Stock Quote Add In VBA Declare and Call
"GTVT06" wrote: On Apr 3, 1:02 pm, Newq wrote: 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSN Stock Quote Add In VBA Declare and Call
On Apr 3, 6:03*pm, Newq wrote:
"GTVT06" wrote: On Apr 3, 1:02 pm, Newq wrote: 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSN Stock Quote Add In VBA Declare and Call
"GTVT06" wrote: On Apr 3, 6:03 pm, Newq wrote: "GTVT06" wrote: On Apr 3, 1:02 pm, Newq wrote: 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 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSN Stock Quote Add In VBA Declare and Call
"Newq" wrote: "GTVT06" wrote: On Apr 3, 6:03 pm, Newq wrote: "GTVT06" wrote: On Apr 3, 1:02 pm, Newq wrote: 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 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MSN Stock Quote | Excel Worksheet Functions | |||
Declare Function for global use then call on change event | Excel Programming | |||
stock quote add in | Excel Discussion (Misc queries) | |||
Declare API call to Windows GetFile | Excel Programming | |||
How do I get a refreshable stock quote for S | Excel Discussion (Misc queries) |