A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to link website data to Excel cells



 
 
Thread Tools Display Modes
  #1  
Old February 17th 05, 10:53 PM
Tony
external usenet poster
 
Posts: n/a
Default 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
Ads
  #2  
Old February 18th 05, 03:08 AM
R.VENKATARAMAN
external usenet poster
 
Posts: n/a
Default

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





  #3  
Old February 18th 05, 04:05 PM
Tony
external usenet poster
 
Posts: n/a
Default

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

  #4  
Old September 28th 05, 04:11 PM
JVLin
external usenet poster
 
Posts: n/a
Default

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

  #5  
Old September 28th 05, 04:16 PM
JVLin
external usenet poster
 
Posts: n/a
Default

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i copy columns of data in notepad into microsoft excel? Jason Excel Discussion (Misc queries) 3 February 12th 05 02:04 AM
How to sort/update large excel db [email protected] Excel Discussion (Misc queries) 0 February 2nd 05 12:43 AM
Can I create a static link between Excel and Outlook contact data. Bokeltri Excel Discussion (Misc queries) 3 January 31st 05 01:57 PM
Fetching External Data from Excel Sri Excel Discussion (Misc queries) 2 January 3rd 05 11:46 AM
Automatic copying data excluding blank cells Wesley Excel Worksheet Functions 6 November 30th 04 01:17 AM


All times are GMT +1. The time now is 08:41 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.