Power Query And API Loop to pull multiple pages

[ZEPBCOB77]

Hi,

I have tried a few different things and cannot seem to get it to work . I want to pulll 10-20 pages of data from a FINVIZ Screener and I can only get the first page to pull .

I am using  a blank query or trying to pull from the web as a data source.   I am guessing I need a function but cannot seem to get it to work.  Web link is below 

Thanks

https://elite.finviz.com/screener.ashx?v=151&f=ta_rsi_os40&ft=3&c=1,2,3,11,12,24,,25,26,27,28,29,30,...

[v-venuppu]

Hi @ZEPBCOB77 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @ZEPBCOB77 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @ZEPBCOB77 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MarkLaf @lbendlin for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[MarkLaf · Accepted solution]

All you need is to add r=start_row to your query. Here is a query with Offset and NumberOfPages parameters. I also incorporated parsing of the table - obviously this part can break easily if the underlying html gets changed in an update.

let
    // screener data parameters
    Param_View = "151",
    Param_Filter = "ta_rsi_os40",
    Param_Order = "ticker",
    
    // screener paging parameters
    Param_Offset = 2,
    Param_NumberOfPages = 4,

    // screener constants - do not change
    Constant_PageSize = 20,

    // query to get pages
    GetPages = List.Generate(
        ()=>0,
        each _ < Param_NumberOfPages,
        each _ + 1,
        each [
            get = Web.Contents(
                "https://finviz.com/screener.ashx",
                [
                    Query = [
                        v=Param_View,
                        f=Param_Filter,
                        o=Param_Order,
                        r=Text.From( 
                            Param_Offset * Constant_PageSize 
                            + _ * Constant_PageSize + 1 
                        )
                    ]
                ]
            ),
            // convert binary to html text for parsing
            html = Text.FromBinary( get ),
            // parse html into tabular data
            // this relies on a particular html structure and is thus brittle
            screener_headers = Html.Table( 
                html, {{"Headers","*"}}, 
                [RowSelector="table.screener_table > thead th"] 
            )[Headers],
            screener_fieldcount = List.Count(screener_headers),
            screener_rows = List.Split( 
                Html.Table( 
                    html, {{"Rows","*"}}, 
                    [RowSelector="table.screener_table tr td"] 
                )[Rows], 
                screener_fieldcount 
            ),
            screener_rowschema = Record.FromList( 
                List.Repeat({[Type=type text,Optional=false]},screener_fieldcount), 
                screener_headers 
            ),
            screener_tabletype = type table Type.ForRecord(screener_rowschema, false),
            screener_table = Table.FromRows(screener_rows,screener_tabletype)
        ] [screener_table]
    ),
    CombinePages = Table.Combine( GetPages )
in
    CombinePages

Notes:

1. Data source settings that worked for me in testing above:
   
2. Check the terms of service. They have their api behind a paywall, so I wouldn't be surprised if they had language that limited certain types of interactions (e.g. web scraping) on their free screener.
3. All data coming through html is going to be text. The above does not handle the type transformations as I assume these will change depending on view/columns.
4. The screener page includes the total number of records in the filter. You can parse this from html and use to set the exact number of pages needed to get all records rather than blindly guessing (if your goal is to get all records rather than top 200 for example)
   
5. There is a more optimal approach that would include querying the first page separately to get: total number of records, screener table headers. Then, you could use List.Generate approach I use above to get all subsequent pages while a) leveraging total number of records to get all records or prevent querying a bunch of blank tables if you go over max pages, and b) pass through the table schema rather than doing it for each query as we are now

[lbendlin]

Find the API provided by finviz and run your queries against that API.  Screen scraping a dynamic HTML5 page will not be successful.