PDA

View Full Version : Webpage linked with MS Excel


nikeshtnt
May 13, 2015, 09:39 PM
Hi,

I know how to link a webpage with excel but most of the time it don't provide result as required. e.g. if I want a table of share price and chart
1) Currently table format is not customized
2) Chart is not viewable.
I want to prepare the dashboard kind of thing from different websites.
Can someone help me with this so that?

Thanks in advance.

Nikesh

JBeaucaire
May 13, 2015, 10:23 PM
If you're talking about stock prices, the web is full of functions that pull stock prices from web sites. Here's one I've used:


Function Shareprice(YahooTicker As String)
Dim strURL As String, strCSV As Double, http As Object

' Compile the request URL with selected Yahoo Ticker & Yahoo Tag
' Example for Apple Inc. share price
' http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=l1

strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & YahooTicker & "&f=l1"

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.send
strCSV = http.responseText
Shareprice = strCSV

Set http = Nothing

End Function

Install that into a standard code module (module1, module2, etc) and then you
Will have a new function available in your spreadsheet. Assuming you have GOOGL
For instance in A1, then this formula in B1 would pull back that stock's
Price:

=SHAREPRICE(A1)

=SHAREPRICE("GOOGL")

That will always show the current price of that stock. So this stuff is doable, if it's what you're after. Most easily found with simple Google searches.

ScottGem
May 14, 2015, 06:15 AM
If you want to publish a list of prices to a WEB site. It might be easier to export your Excel sheet in HTML format and publish that.

nikeshtnt
May 25, 2015, 12:12 AM
If you want to publish a list of prices to a WEB site. It might be easier to export your Excel sheet in HTML format and publish that.

Hi ScottGem,

Thanks for your reply.

However my query is different from your answer. I want to import from web to excel and format it as per my requirement


If you're talking about stock prices, the web is full of functions that pull stock prices from web sites. Here's one I've used:


Function Shareprice(YahooTicker As String)
Dim strURL As String, strCSV As Double, http As Object

' Compile the request URL with selected Yahoo Ticker & Yahoo Tag
' Example for Apple Inc. share price
' http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=l1

strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & YahooTicker & "&f=l1"

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.send
strCSV = http.responseText
Shareprice = strCSV

Set http = Nothing

End Function

Install that into a standard code module (module1, module2, etc) and then you
Will have a new function available in your spreadsheet. Assuming you have GOOGL
For instance in A1, then this formula in B1 would pull back that stock's
Price:

=SHAREPRICE(A1)

=SHAREPRICE("GOOGL")

That will always show the current price of that stock. So this stuff is doable, if it's what you're after. Most easily found with simple Google searches.

Thanks

Can this be done without macro or how I can change the web address if I want data from different website.

JBeaucaire
May 25, 2015, 04:00 PM
This is a turnkey solution that I've used for this purpose. You would need to Google up methods for use on other sites, to me it would be a waste of time. But I found this solution with simple Google Search so I am confident you could uncover one as well.

Or just use this one, it's done and it works.

No, you cannot pull data from websites into Excel without the use of VBA.