scraping data from finviz

Occasional Visitor

hello, i am currently having hard time pulling specific data on the finviz.com table utilizing
Data>get data From Web>URL Link"https://elite.finviz.com/quote.ashx?t=expe&ty=c&ta=1&p=d"
then click on table 0 >select transform data
>right click on individual portion of table select-add as new query
once am ready to put on my excel, this is where i am having problem is it always overlapping my original work and over write my original excel. all i want is to get this individual data into one of my work to update automatically from the original website source. i could not find a proper formula to get it working either.

i used another formula on google sheets

=index(IMPORTHTML("http://finviz.com/quote.ashx?t="&"AAPL","table", 11),2,2)


and its very simple and work every time. does anyone know of a formula that will make this work for excel?

2 Replies

Hi @nicol320,

 

Please note that when you click refresh Power Query will refresh the and get you the latest update. Your challenge here is to source should not get update. Well understanding your query I can propose you two solution. 

  1. Once your data is loaded into Excel delete the connection to you table 
  2. Copy & paste the data to new worksheet as values only, You can configure a macro to do that activity once the data is refreshed.

 Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

@nicol320 Perhaps you mean that when you load a query result to an Excel table, that it overwrites existing data in the target sheet. If so, make sure that the External Data Properties are set to insert cells/rows, rather than to overwrite.

Screenshot 2020-08-24 at 10.36.15.png