How to link website data to Excel cells
I want to automatically import wesbite data in Excel cells, to perform
calculations. For example, I could have a portfolio in Excel that does complex calculations. The portfolio needs to update stock prices every day (and other numerical information). Don't want to manually cut, format and paste every day for dozens of stocks and other values. Want to link up the stock price in Yahoo (or from Fidelity Investments) to the cell automatically, such that every day I can open Excel and ask it to refresh all the prices at once. Is this possible? Thanks for your help. Tony from Houston |
in excel spreadsheetuse ---data(menubar)-getexternaldata-newwebquery.
you can get all the tables or particualr table in which you must inow the number of that table in the webpage. once the query is created subsequetly you have to only refresh see help <get external data Tony wrote in message ... I want to automatically import wesbite data in Excel cells, to perform calculations. For example, I could have a portfolio in Excel that does complex calculations. The portfolio needs to update stock prices every day (and other numerical information). Don't want to manually cut, format and paste every day for dozens of stocks and other values. Want to link up the stock price in Yahoo (or from Fidelity Investments) to the cell automatically, such that every day I can open Excel and ask it to refresh all the prices at once. Is this possible? Thanks for your help. Tony from Houston |
Thank You R.VENKATARAMAN.
Follow up question. For "New Web Query" command, how do I know the number or name of the table on the website? I did some trial and error and finally found the number of the desired table, like 64, clearly this is time consuming because the number could be in the dozens or even hundreds. Another question. What if I only want certain values from the table and not the entire table with text and all? How do I link up Excel to only the values that I want? Thanks Again "Tony" wrote: I want to automatically import wesbite data in Excel cells, to perform calculations. For example, I could have a portfolio in Excel that does complex calculations. The portfolio needs to update stock prices every day (and other numerical information). Don't want to manually cut, format and paste every day for dozens of stocks and other values. Want to link up the stock price in Yahoo (or from Fidelity Investments) to the cell automatically, such that every day I can open Excel and ask it to refresh all the prices at once. Is this possible? Thanks for your help. Tony from Houston |
Hi Tony,
if you turn on the macro recorder and than insert or edit the web query, you will record this kind of code: With Selection.QueryTable .Connection = _ "URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?lstStatement=Balance&Symbol=" & stID & "&stmtView=Ann" .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "10" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With The number you are looking for under WebTables. Regards, J "Tony" wrote: Thank You R.VENKATARAMAN. Follow up question. For "New Web Query" command, how do I know the number or name of the table on the website? I did some trial and error and finally found the number of the desired table, like 64, clearly this is time consuming because the number could be in the dozens or even hundreds. Another question. What if I only want certain values from the table and not the entire table with text and all? How do I link up Excel to only the values that I want? Thanks Again "Tony" wrote: I want to automatically import wesbite data in Excel cells, to perform calculations. For example, I could have a portfolio in Excel that does complex calculations. The portfolio needs to update stock prices every day (and other numerical information). Don't want to manually cut, format and paste every day for dozens of stocks and other values. Want to link up the stock price in Yahoo (or from Fidelity Investments) to the cell automatically, such that every day I can open Excel and ask it to refresh all the prices at once. Is this possible? Thanks for your help. Tony from Houston |
Re certain numbers from a table:
I simply link the destination cell in my output table: e.g. the query table might fill range c1:g12 and the item I want is in d5. I want this to appear in my output table A1:b5 in cell b3. In cell B3 I write '= D5' Presto! j. "Tony" wrote: Thank You R.VENKATARAMAN. Follow up question. For "New Web Query" command, how do I know the number or name of the table on the website? I did some trial and error and finally found the number of the desired table, like 64, clearly this is time consuming because the number could be in the dozens or even hundreds. Another question. What if I only want certain values from the table and not the entire table with text and all? How do I link up Excel to only the values that I want? Thanks Again "Tony" wrote: I want to automatically import wesbite data in Excel cells, to perform calculations. For example, I could have a portfolio in Excel that does complex calculations. The portfolio needs to update stock prices every day (and other numerical information). Don't want to manually cut, format and paste every day for dozens of stocks and other values. Want to link up the stock price in Yahoo (or from Fidelity Investments) to the cell automatically, such that every day I can open Excel and ask it to refresh all the prices at once. Is this possible? Thanks for your help. Tony from Houston |
All times are GMT +1. The time now is 03:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com