Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Web Query
Thank you in advance for any help provided to the subject question. I am
operating Excel 2003. A data query has been established to pull investment data from finance.yahoo.com (i.e. stock volume, % ownership of management, etc.). The query works fine and pulls approximately 50 values from the webpage. My questions is this: If I have a list of 100 stock symbols how can I link that list to the web based data query to automatically insert each symbol into the data query individually and then dump that data automatically to another worksheet within the same workbook. Should I use a marco, pivot table, etc. I also have a follow up question if time pemits. If I have a drop down box within a worksheet how do I link that to the data query so I can simply pick an individual stock symbol and then have the data query pull the related data from the web? Thank you again for any help provided to this question. |
#2
|
|||
|
|||
1.is the query address is different for each scrip.
2.can each scrip data confined to one row in which case you can create a vba macro after downloading the data the data can be transfered to master sheet that is atleast what I am doing for my scrips in <in.finance.yahoo.com there are also other ways in in.finance.yahoo.com -- remove $$$ from email addresss to send email ========= "ebgehringer" wrote in message ... Thank you in advance for any help provided to the subject question. I am operating Excel 2003. A data query has been established to pull investment data from finance.yahoo.com (i.e. stock volume, % ownership of management, etc.). The query works fine and pulls approximately 50 values from the webpage. My questions is this: If I have a list of 100 stock symbols how can I link that list to the web based data query to automatically insert each symbol into the data query individually and then dump that data automatically to another worksheet within the same workbook. Should I use a marco, pivot table, etc. I also have a follow up question if time pemits. If I have a drop down box within a worksheet how do I link that to the data query so I can simply pick an individual stock symbol and then have the data query pull the related data from the web? Thank you again for any help provided to this question. |
#3
|
|||
|
|||
Thank you so much for your advice, the answers to your comments are below:
(1) The query address for each stock symbol is different. (2) How do I create the VBA macro you speak of? "R.VENKATARAMAN" wrote: 1.is the query address is different for each scrip. 2.can each scrip data confined to one row in which case you can create a vba macro after downloading the data the data can be transfered to master sheet that is atleast what I am doing for my scrips in <in.finance.yahoo.com there are also other ways in in.finance.yahoo.com -- remove $$$ from email addresss to send email ========= "ebgehringer" wrote in message ... Thank you in advance for any help provided to the subject question. I am operating Excel 2003. A data query has been established to pull investment data from finance.yahoo.com (i.e. stock volume, % ownership of management, etc.). The query works fine and pulls approximately 50 values from the webpage. My questions is this: If I have a list of 100 stock symbols how can I link that list to the web based data query to automatically insert each symbol into the data query individually and then dump that data automatically to another worksheet within the same workbook. Should I use a marco, pivot table, etc. I also have a follow up question if time pemits. If I have a drop down box within a worksheet how do I link that to the data query so I can simply pick an individual stock symbol and then have the data query pull the related data from the web? Thank you again for any help provided to this question. |
#4
|
|||
|
|||
the data is like this in the active sheet from 4th row (4th row headings and
5 6 7 data act col A col B col c etc (next line) company name/ yahoo code/ yahoo code/LTP/DATE/TIME/CHANGE/OPEN/HIGH/LOW--these two lines are in 4 throw ABHISHEK IND ABHP.NS =====this is 5th row col A and B ADOR WELDING ADOR.NS =====6th row AKSH OPTIFIBRE AKSO.NS ======7th row after the column headings only col A and B are there. other column automatically filled after the sub is run now run this code Public Sub Aclear() 'this clears the data which are filled due to sub Worksheets("sheet1").Activate Range(Cells(5, 3), Cells(Rows.Count, Columns.Count)).Clear End Sub Public Sub Bdownloaddataxp() Dim source As Range Dim lastcell As Range Dim dest As Range Aclear ' this clears the data from col C to J except headingss Set dest = Range("c5") Set source = Range("B5") Set lastcell = Range("a5").End(xlDown).Offset(1, 0) Dim sNWind As String Do 'the next two line are one line 'source is the variable of the scrip code inorproated in the web url sNWind = "URL;http://in.finance.yahoo.com/d/quotes.csv?s=" & source &"&m=b&f=sl1d1t1c1ohgv&e=.csv" Worksheets("sheet1").Activate Dim oQryTable As Object Set oQryTable = Worksheets("sheet1").QueryTables.Add( _ sNWind & ";", source.Offset(0, 1)) oQryTable.Refresh False source.Offset(0, 1).Copy Set dest = dest.Offset(1, 0) Set source = source.Offset(1, 0) Loop Until source = lastcell Ctexttocol End Sub Public Sub Ctexttocol() Range(Range("C5"), Range("C5").End(xlDown)).Select Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Comma:=True Columns("e:h").NumberFormat = "0.00" Columns("I:I").NumberFormat = "#,##0" Columns("a:k").AutoFit Columns("e:e").NumberFormat = "dd-mmm-yy" Range("a5").Select End Sub after running this code the columns c to J will be filled with formats etc. you can modify to suit you this is what I used to get the current data and daily run this sub after trading is over. naturally daily I copy the downloaded data to a master sheet to give historical data. -- remove $$$ from email addresss to send email =================== "ebgehringer" wrote in message ... Thank you so much for your advice, the answers to your comments are below: (1) The query address for each stock symbol is different. (2) How do I create the VBA macro you speak of? "R.VENKATARAMAN" wrote: 1.is the query address is different for each scrip. 2.can each scrip data confined to one row in which case you can create a vba macro after downloading the data the data can be transfered to master sheet that is atleast what I am doing for my scrips in <in.finance.yahoo.com there are also other ways in in.finance.yahoo.com -- remove $$$ from email addresss to send email ========= "ebgehringer" wrote in message ... Thank you in advance for any help provided to the subject question. I am operating Excel 2003. A data query has been established to pull investment data from finance.yahoo.com (i.e. stock volume, % ownership of management, etc.). The query works fine and pulls approximately 50 values from the webpage. My questions is this: If I have a list of 100 stock symbols how can I link that list to the web based data query to automatically insert each symbol into the data query individually and then dump that data automatically to another worksheet within the same workbook. Should I use a marco, pivot table, etc. I also have a follow up question if time pemits. If I have a drop down box within a worksheet how do I link that to the data query so I can simply pick an individual stock symbol and then have the data query pull the related data from the web? Thank you again for any help provided to this question. |
#5
|
|||
|
|||
i forgot to add run the sub<Bdownloaddataxp
"R.VENKATARAMAN" wrote in message ... the data is like this in the active sheet from 4th row (4th row headings and 5 6 7 data act col A col B col c etc (next line) company name/ yahoo code/ yahoo code/LTP/DATE/TIME/CHANGE/OPEN/HIGH/LOW--these two lines are in 4 throw ABHISHEK IND ABHP.NS =====this is 5th row col A and B ADOR WELDING ADOR.NS =====6th row AKSH OPTIFIBRE AKSO.NS ======7th row after the column headings only col A and B are there. other column automatically filled after the sub is run now run this code Public Sub Aclear() 'this clears the data which are filled due to sub Worksheets("sheet1").Activate Range(Cells(5, 3), Cells(Rows.Count, Columns.Count)).Clear End Sub Public Sub Bdownloaddataxp() Dim source As Range Dim lastcell As Range Dim dest As Range Aclear ' this clears the data from col C to J except headingss Set dest = Range("c5") Set source = Range("B5") Set lastcell = Range("a5").End(xlDown).Offset(1, 0) Dim sNWind As String Do 'the next two line are one line 'source is the variable of the scrip code inorproated in the web url sNWind = "URL;http://in.finance.yahoo.com/d/quotes.csv?s=" & source &"&m=b&f=sl1d1t1c1ohgv&e=.csv" Worksheets("sheet1").Activate Dim oQryTable As Object Set oQryTable = Worksheets("sheet1").QueryTables.Add( _ sNWind & ";", source.Offset(0, 1)) oQryTable.Refresh False source.Offset(0, 1).Copy Set dest = dest.Offset(1, 0) Set source = source.Offset(1, 0) Loop Until source = lastcell Ctexttocol End Sub Public Sub Ctexttocol() Range(Range("C5"), Range("C5").End(xlDown)).Select Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Comma:=True Columns("e:h").NumberFormat = "0.00" Columns("I:I").NumberFormat = "#,##0" Columns("a:k").AutoFit Columns("e:e").NumberFormat = "dd-mmm-yy" Range("a5").Select End Sub after running this code the columns c to J will be filled with formats etc. you can modify to suit you this is what I used to get the current data and daily run this sub after trading is over. naturally daily I copy the downloaded data to a master sheet to give historical data. -- remove $$$ from email addresss to send email =================== "ebgehringer" wrote in message ... Thank you so much for your advice, the answers to your comments are below: (1) The query address for each stock symbol is different. (2) How do I create the VBA macro you speak of? "R.VENKATARAMAN" wrote: 1.is the query address is different for each scrip. 2.can each scrip data confined to one row in which case you can create a vba macro after downloading the data the data can be transfered to master sheet that is atleast what I am doing for my scrips in <in.finance.yahoo.com there are also other ways in in.finance.yahoo.com -- remove $$$ from email addresss to send email ========= "ebgehringer" wrote in message ... Thank you in advance for any help provided to the subject question. I am operating Excel 2003. A data query has been established to pull investment data from finance.yahoo.com (i.e. stock volume, % ownership of management, etc.). The query works fine and pulls approximately 50 values from the webpage. My questions is this: If I have a list of 100 stock symbols how can I link that list to the web based data query to automatically insert each symbol into the data query individually and then dump that data automatically to another worksheet within the same workbook. Should I use a marco, pivot table, etc. I also have a follow up question if time pemits. If I have a drop down box within a worksheet how do I link that to the data query so I can simply pick an individual stock symbol and then have the data query pull the related data from the web? Thank you again for any help provided to this question. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Query not installed for New Database Query | Excel Discussion (Misc queries) | |||
Web Query Help... | Excel Discussion (Misc queries) | |||
"Query cannot be edited by the Query Wizard" | Excel Discussion (Misc queries) | |||
Query of External Data | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |