[Invoke Custom Function] Problem with getting the web data for each row

[FredF]

Hi guys,

I would like to get the stock split history webdata for each ticker. As you can see (1. Data StockSplitHistory), each row is a different ticker so i would expect different output. Now it shows all the same output, probably because in my function the symbol = adtx and that needs to be a variabel according to the specific ticker for each row.

FunctionStockSplitHistory

(Ticker as text) =>
let
Source = Web.Page(Web.Contents("https://www.stocksplithistory.com/?symbol=adtx")),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Ratio", type text}})
in
#"Changed Type1"

Maybe i need to work with loops of some sort? 

Could you please help me out here? 

Thanks a million! 

Regards, Eric 

[FredF · Accepted solution]

Thank you for your reply! 

Everything is working now! 

[AntrikshSharma]

@FredF Fred mark my reply as solution.

[AntrikshSharma]

@FredF First I wrote a Python script to get all the links:

import requests
from bs4 import BeautifulSoup
import pandas as pd

url = 'https://www.stocksplithistory.com/'
reqs = requests.get(url)
soup = BeautifulSoup(reqs.text, 'html.parser')
 
tickers = []
for link in soup.find_all('a'):
    current_link = link.get('href')
    if 'stocksplithistory' in current_link or 'splithistory' in current_link:
        tickers.append(current_link)

ticker_df = pd.DataFrame(tickers, columns = ['Ticker'])
ticker_df

Then fixed the function to check for Date and Ratio column as some pages have 2 tables some have 3, your code has hardcoded {1} i.e. Source{1}[Data] which doesn't work for some links.

( TickerLink as text ) =>
    let
        Source = Web.Page ( Web.Contents ( TickerLink ) ),
        AddedCustom = 
            Table.AddColumn (
                Source,
                "Custom",
                each List.ContainsAll (
                    List.Combine ( Table.ToColumns ( [Data] ) ),
                    { "Date", "Ratio" }
                )
            ),
        FilteredRows = Table.SelectRows ( AddedCustom, each ( [Custom] = true ) )[[Data]],
        ExpandedData = 
            Table.ExpandTableColumn (
                FilteredRows,
                "Data",
                { "Column1", "Column2" },
                { "Column1", "Column2" }
            ),
        ChangedType = Table.TransformColumnTypes ( ExpandedData, { { "Column1", type text } } ),
        RemovedTopRows = Table.Skip ( ChangedType, 1 ),
        PromoteHeaders = Table.PromoteHeaders ( RemovedTopRows, [ PromoteAllScalars = true ] ),
        ChangedType1 = 
            Table.TransformColumnTypes (
                PromoteHeaders,
                { { "Date", type date }, { "Ratio", type text } }
            )
    in
        ChangedType1

Finally invoked the function

let
    Source = Tickers,
    InvokedCustomFunction = 
        Table.AddColumn (
            Source,
            "FxGetData",
            each try FxAntriksh ( [Ticker] ) otherwise null,
            type table [ Date = date, Ratio = text ]
        ),
    RemoveNulls = Table.SelectRows ( InvokedCustomFunction, each [FxGetData] <> null ),
    ExpandedFxGetData = 
        Table.ExpandTableColumn (
            RemoveNulls,
            "FxGetData",
            { "Date", "Ratio" },
            { "Date", "Ratio" }
        )
in
    ExpandedFxGetData

 If you can't use Python for xyz reason then I have hardcoded the values in a separate query TickersHardCoded.

Finally, the code is slow, so it will take 5-10 minutes to complete the refresh.

Here is the link to the PBIX - https://drive.google.com/file/d/1o4vSUyQP0Ex-GcsLPEZLDsjGx800k4eG/view?usp=sharing