The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I've been trying to get financial data (Income Statement, in particular) relating to public stocks from Alphavantage API, which is free.
https://www.alphavantage.co/documentation/
This is the M code i've used
Note: in row 3, it represent my personal computer path where I had created an Excel file with several stock tickers (i.e. AAPL, MSFT, TSLA), to which I could add tickers and the data in Power BI would extract additional financials automatically).
The later text in red (123....) represents the unique user/API key that you get for free from Alphavantage.
let
// Step 1: Load the list of unique identifiers from the Excel file
Source = Excel.Workbook(File.Contents("DummyTextWhereIusedMyPersonalDrive"), null, true),
SP500_Sheet = Source{[Item="SP500",Kind="Sheet"]}[Data],
// Step 2: Transform the data to a list of unique identifiers
UniqueIdentifiers = SP500_Sheet[Column1],
// Step 3: Define function to fetch data for a single company
GetCompanyData = (symbol as text) =>
let
Source = Json.Document(Web.Contents("https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=" & symbol & "&apikey=12345678.....")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded quarterlyReports" = Table.ExpandListColumn(#"Converted to Table", "quarterlyReports"),
#"Expanded quarterlyReports1" = Table.ExpandRecordColumn(#"Expanded quarterlyReports", "quarterlyReports", {"fiscalDateEnding", "reportedCurrency", "grossProfit", "totalRevenue", "costOfRevenue", "costofGoodsAndServicesSold", "operatingIncome", "sellingGeneralAndAdministrative", "researchAndDevelopment", "operatingExpenses", "investmentIncomeNet", "netInterestIncome", "interestIncome", "interestExpense", "nonInterestIncome", "otherNonOperatingIncome", "depreciation", "depreciationAndAmortization", "incomeBeforeTax", "incomeTaxExpense", "interestAndDebtExpense", "netIncomeFromContinuingOperations", "comprehensiveIncomeNetOfTax", "ebit", "ebitda", "netIncome"}, {"fiscalDateEnding", "reportedCurrency", "grossProfit", "totalRevenue", "costOfRevenue", "costofGoodsAndServicesSold", "operatingIncome", "sellingGeneralAndAdministrative", "researchAndDevelopment", "operatingExpenses", "investmentIncomeNet", "netInterestIncome", "interestIncome", "interestExpense", "nonInterestIncome", "otherNonOperatingIncome", "depreciation", "depreciationAndAmortization", "incomeBeforeTax", "incomeTaxExpense", "interestAndDebtExpense", "netIncomeFromContinuingOperations", "comprehensiveIncomeNetOfTax", "ebit", "ebitda", "netIncome"})
in
#"Expanded quarterlyReports1",
// Step 4: Fetch data for each company in the list
CompanyData = List.Transform(UniqueIdentifiers, each GetCompanyData(_))
in
CompanyData
I keep getting the error "The column 'quarterlyReports' of the table wasn't found"
Which may be due to the fact that you get two lists / tables to choose from? (annualReports and quarterly reports)
I've tried multiple things trying to adjust the M code, but with no luck
I would highly appreciate if anyone's encountered this or know how to solve it
Best,
Martynas
Column1 (and you can see the error, which contains "quarterlyReport"). Unfortunately, I can't simply just remove errors, as when I do even though everything looks fine in PQ, when I apply and load I get the aforementioned error in Desktop.
Remove errors and then expand the column. Read about how to handle missing columns
I might be missunderstanding something, but as I just said, this approach didn't work as I've tried it
which columns do you see in the #"Converted to Table" step?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |