Stock Ticker from Web Question

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
217
I want to bring in some data from a website based on a stock ticker of my choosing, below is the code. Is it possible to change the eng (stock ticker) to a cell reference? For example if I type something in cell A1 and refresh query it would change that ENG to whatever is in cell A1 on a sheet called ticker?

let
Source = Web.Page(Web.Contents("http://www.marketwatch.com/investing/stock/eng/financials")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Fiscal year is January-December. All values USD millions.", type text}, {"2011", type text}, {"2012", type text}, {"2013", type text}, {"2014", type text}, {"2015", type text}, {"5-year trend", type text}})
in
#"Changed Type"
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
If you define a name for cell A1 (select cell A1 and replace "A1" in the box left of the formula with e.g. StockTicker), this will work:

Code:
let
    StockTicker = Table.FirstValue(Excel.CurrentWorkbook(){[Name="StockTicker"]}[Content]),
    Source = Web.Page(Web.Contents("[URL]http://www.marketwatch.com/investing/stock/[/URL]"& StockTicker&"/financials")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Fiscal year is January-December. All values USD millions.", type text}, {"2011", type text}, {"2012", type text}, {"2013", type text}, {"2014", type text}, {"2015", type text}, {"5-year trend", type text}})
in
    #"Changed Type"
 
Upvote 0
THANK YOU!!!! Is there a reason Power Query run very slowly retrieving this information? It takes well over two minutes to return 12 rows and 7 columns
 
Upvote 0
Table.Buffer might help:

Code:
let
    StockTicker = Table.FirstValue(Excel.CurrentWorkbook(){[Name="StockTicker"]}[Content]),
    Source = [B]Table.Buffer([/B]Web.Page(Web.Contents("[URL]http://www.marketwatch.com/investing/stock/[/URL]"& StockTicker&"/financials"))[B])[/B],
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Fiscal year is January-December. All values USD millions.", type text}, {"2011", type text}, {"2012", type text}, {"2013", type text}, {"2014", type text}, {"2015", type text}, {"5-year trend", type text}})
in
    #"Changed Type"
 
Last edited:
Upvote 0
Thats better!!! Last question I promise ( I am really trying to learn the ins and outs of Power Query, I have gotten a lot better from .txt, and .xls files. This is my first journey into web pages). Can I make it auto update every time the StockTicker cell changes? Or do I need to refresh the query each time?
 
Upvote 0
That would require a macro that runs upon a change in the worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("StockTicker")) Is Nothing Then Exit Sub
ActiveWorkbook.Connections("Query - Query1").Refresh

End Sub

Video showing where to put this code: https://youtu.be/5qeiv48dK8o
 
Upvote 0
I figured it would take a macro, I am trying to stay away from that but may just go ahead and do it. Thanks for all of your help, may I ask a good resource to figure things out on my own regarding power query rather than bugging people on this board every time I get hung up?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top