Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
Thank you for your reply!
Everything is working now!
@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