Powerquery data extraction from website question

[Doofus1]

Hi Folks,
I was shown how to extract data from a table on the website www.finviz.com
I now have a new problem I'd like help with.
I have uploaded an Excel file and a PDF of a page on the finviz.com website describing what I would like to achieve.
For this issue, I'd like to know how to extract data from several tabs, simultaneously, onto a single excel spreadsheet. In this case, sheet 2 of the uploaded excel file.
On the excel spreadsheet, on Sheet 2, in cell A2, I would like to enter the word/s that describe an industry, refresh the data, and have the data from the tabs OVERVIEW, VALUATION, FINANCIAL, OWNERSHIP, PERFORMANCE, TECHNICAL and CUSTOM appear on that sheet starting with cell B2.
Ideally, I'd like too see the data load on to the page horizontally eg columns B2 through CZ800.
In addition, I would need ALL the data that may be on one page or several pages to appear on this page.
If anyone knows how to do this, and could format the sheet for me, it would be great.
If anyone can do that, and then, provide detailed instructions on how that was done or at least answer my questions about how that was achieved, would be helping to educate an amateur - that would be fantastic.
Thank you
Doofus.

[AliGW]

Good morning!

Your post today reads rather like a brief: that's the sort of thing we'd expect to see in the Commercial Services section, where you offer payment to someone to realise a project for you.

The idea here, in the free section, is that we help you towards a goal: rather than asking us to do it for you and then provide detailed instructions, you should be working with us towards the goal.

So, you will realise now what my question is: what have you tried so far? It is not that you are completely without knowledge of PowerQuery, and so I'd expect to see you trying to employ some of the tricks you have learnt here already to get you at least part of the way.

Anyone working with you on this will need the website credentials so that they can access the data (i.e. login details).

[Doofus1]

Morning, AliGW,
Sorry about the way I wrote that post.
I am looking for someone to guide me here.
When I mentioned "detailed instructions" I said that because I did not want someone to fix the sheet and have me thank them.
I am into working towards the goal thing - ie using something I understand (the finviz.com) site, to make a tedious job easier - which adds to my understanding of PQ.
I don't subscribe to this site. So, no one needs login credentials. I have not reached that level of expertise or insanity - yet.
It is just a great site with a lot of free information.
All that data, on that site, is available for free - unless you select the "Custom" option (that's when you have to subscribe to the site and have a login and password).
It also has a lot of pop up ads.
I want to bring the free data onto an excel sheet so that I can look at it in peace.
For example, accessing the information for all companies in an industry will enable me to figure out the average price of shares in an industry or figure out how may of the companies pay a dividend. Incidentally, I am no Gordon Gekko or a Wolf of Wallstreet. I am more a sheep of Sesame Street.
What have I tried?, the trick that Alansidman showed me with the the data on sheet 1. It did not work for me for this purpose.
So, if you or anyone else can outline a starting point or how I can initiate the process, I'd appreciate it.
I am reading the book M is for (data) monkey -on Ch2 but so far they are only working with CSV files. My question is about data extraction, transformation of and loading of data from a website to an excel sheet.
Thank you
Doofus

Be back in about 8hrs.

[AliGW]

All that data, on that site, is available for free

Well, the query won't load the data for me because it is looking for login credentials.

[Doofus1]

Hi AliGW,
If anyone uses the "Custom (Elite only)" filter option, login credentials will be required.
For that, payment is required.
I am not a subscriber to the site.
Normally, I'd select the SCREENER tab (the tab running across/near the top of the home page).
Then, on the filters tab, I'd select the ALL tab.
Set whatever filters I want and look at the data.
Trouble is there are a ton of pop-up ads.
So, Alansidman's trick works to avoid that - when looking at the data for an individual company.
But now, I want to look at that data for all the companies (see the PDF and Excel sheet examples I uploaded earlier) in a given industry (on an excel spreadsheet).
I can keep copying and pasting.
However, given what I think PQ can do, I was wondering if I can select an industry name (except/excluding CUSTOM (ELITE ONLY)) and have the data (under all the selected tabs - OVERVIEW to CUSTOM) appear on an excel spread sheet.
If it can't be done, I will keep copying and pasting.
I'd like to keep this thread open a little longer in case someone else has any ideas.
Thank you
Doofus

[alansidman]

This can be done in Power Query. However, you will need to create a query with two parameters. Use the format shown in this video to set up
1) each of the Screens you wish, ie. Ownership, valuation, etc
2) type of business, ie. Tobacco, Beverage, Grocery, etc.


Give this a try as you have my model from earlier and this video. Post back as you move along with issues you may have.

https://www.youtube.com/watch?v=gK2yBpiITvI

[Doofus1]

Hi Alansidman,
Thank you.
Will do and report on my progress.
Thank you
Doofus

[Doofus1]

Hi Alansidman,
Still clueless.
I have uploaded an excel sheet with:
1. What I attempted - using your earlier instructions and what I think I understood about the video.
2. A mock up of how I'd like to see the data appear.

I know that there is some m code to edit in the editor for the table and the path - but have no clue as to what.

There is a fair amount of possible data duplication too - but that is immaterial as long as I can get the data onto the spreadsheet.
So, I think I am trying to fly before I can crawl.
I need help - if you have the time.
Thank you
Doofus.

[alansidman]

Here is my first play with this data.

You will need to set up your parameters based upon the exact spelling in the screen URL. Not the drop down on the web. You will need to insert the parameter into the Path. Look at how I did this in the attached. The type of screen will also change, but I don't have time for that now. Will get back to this as soon as I can. Probably not for a few days as I have other commitments

[Doofus1]

Hi Alansidman,
I am in a similar predicament.
Thus, I cannot indulge this hobby for the next few days either.
Meanwhile, I have downloaded your example and will meditate on the M code when I get a moment.
One question for the time being - how do I insert the parameter into the path?
Do I go to the Formulas tab, > Define Name > add the name as it appears in the screen URL and select OK?
Sorry about the dumb question.
Best
Doofus

Sorry. My bad.
The answer to my question is on the youtube link you sent.

[Doofus1]

Hi Alansidman,
I have added all the parameters to the uploaded sheet.
When you have the time, could you instruct me on how to bring all or the relevant columns of the data, from the other tabs, onto the spreadsheet?
Thank you
Doofus.

* I have excluded the "Any" and "Custom Elite only" parameters because:
1. "Any" has all the pages and it is not what I want to see.
2 "Custom Elite only" requires a paying subscription.

[Doofus1]

Hi Alansidman,
So, I created a new excel sheet.
I created connections too all the tabs and selected the columns of data I wanted.
I entered all the Industry parameters on sheet 3
I then attempted to create a connection to query the parameters.
And that's when things went wrong for me.
When you have the time, instruct as appropriate.
Thank you
Doofus

[alansidman]

I believe that I have fixed your issues.
1. Changed the parameter query to "Business"
2. In all queries, I eliminated the errors by removing the "Change Type" line
3. In the path to the query, I removed the specific industry to the term "Business" in the Mcode.

See the attached. I believe it is now how you wish to see it.
BTW
If there are more than 20 stocks in any one business group, because of how the web site is configured, you will only get the first 20 companies or stocks. Don't know if that is fixable.

[Doofus1]

Hi Alansidman,
No worries about not getting data on more than 20 companies/stocks.
This is my exercise for learning about PQ.
However, on the sheet you sent me, only columns AB:BU appear to change when I refresh it.
Am I doing something crazy again?
I checked the query options to see that the Privacy option is set to - Always ignore Privacy Level settings. Because that was an issue the last time you helped me with an exercise using this site.
Thank you
Doofus.

[alansidman]

It was my bad. In the first couple of queries the path was missing an "_" that was confusing the program. All is well now I believe. I added a refresh button so that you don't have to PQ to refresh. This also makes the file Macro enabled so it must be saved as an .xlsm file.

[Doofus1]

Hi Alansidman,
Fancy!!
Thank you.
Now, I am going to be extremely ungrateful and ask if, when you have time, you could put this down as a series of instructions - as did for a prior exercise.
This is so that I can practice repeatedly.
I only ask because if you don't ask, you don't get and the worst that can happen is that you say "NO!".
But, that aside, thank you again!!
Doofus

[alansidman]

So you want a step by step instruction. Give me a day or two. Time is freeing up late tomorrow and into Thursday

[Doofus1]

Hi Alansidman,
THANK YOU!!
Please take your time.
PQ is my new hobby.
It goes well with my love of sports, sports stats, the stock market and stock stats.
Meanwhile, I am reading M is for Data Monkey.
It all works best when the examples are relatable to my interests.
Thank you

[alansidman]

Hope the attached helps you to understand the changes I made. Keep up on PQ and it will help you.

[Doofus1]

Hi Alansidman,
This is exactly what I was looking for.
Thank you for taking the time to do this.
Best regards
Doofus

[alansidman]

You are most welcome. If this concludes your issue, please mark the thread as solved.