Excel Power Query

Excel Power Query Setup

Connect your spreadsheet to live SEC EDGAR data. Auto-refreshes when new filings drop.

Open Get Data → From Web

In Excel, go to Data tab → Get Data → From Other Sources → From Web. In the URL field, paste your Valuein endpoint.

📸

Excel ribbon: Data tab → Get Data → From Web dialog

Screenshot coming soon

Paste the API Endpoint

Use this URL pattern. Replace the ticker and form_type with your target company and filing type. The period parameter accepts: 1y, 3y, 5y, 10y, or max.

text

https://data.valuein.biz/v1/fundamentals?ticker=AAPL&form_type=10-K&period=5y

Add Your API Key Header

In the 'From Web' dialog, click 'Advanced'. Under HTTP request headers, add the key X-API-KEY with your Valuein API key as the value. Never put the API key in the URL.

📸

Excel 'From Web' dialog → Advanced → HTTP Headers section → Add X-API-KEY header

Screenshot coming soon

Transform the JSON Response

Power Query will load the JSON response. Click the List column → 'Expand to New Rows', then expand the Record column to get individual fields. Set data types: dates as Date, numbers as Decimal Number.

📸

Power Query editor: expand List → expand Record → set column types

Screenshot coming soon

Copy-Pasteable M-Code

For a repeatable, maintainable connection, use the Advanced Editor in Power Query. This M-Code handles authentication, query parameters, response parsing, and type casting in one block.

powerquery

let
    ApiKey = "vi_live_your_api_key_here",
    Ticker = "AAPL",
    FormType = "10-K",

    Source = Json.Document(
        Web.Contents(
            "https://data.valuein.biz/v1/fundamentals",
            [
                Query = [
                    ticker = Ticker,
                    form_type = FormType,
                    period = "10y",
                    fields = "period_end,revenue,net_income,eps_diluted,gross_profit,operating_income"
                ],
                Headers = [
                    #"X-API-KEY" = ApiKey,
                    #"Accept" = "application/json"
                ]
            ]
        )
    ),

    Data = Source[data],
    Table = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(
        Table, "Column1",
        {"period_end", "revenue", "net_income", "eps_diluted", "gross_profit", "operating_income"}
    ),
    TypedTable = Table.TransformColumnTypes(Expanded, {
        {"period_end", type date},
        {"revenue", type number},
        {"net_income", type number},
        {"eps_diluted", type number},
        {"gross_profit", type number},
        {"operating_income", type number}
    })
in
    TypedTable

Multi-Ticker Query

To pull data for multiple tickers in one query (e.g., FANG comparable analysis), use a parameterized helper function and Table.Combine.

powerquery

let
    ApiKey = "vi_live_your_api_key_here",
    Tickers = {"AAPL", "MSFT", "GOOGL", "AMZN", "META"},

    FetchTicker = (ticker as text) =>
        let
            Source = Json.Document(
                Web.Contents(
                    "https://data.valuein.biz/v1/fundamentals",
                    [
                        Query = [ticker = ticker, form_type = "10-K", period = "3y"],
                        Headers = [#"X-API-KEY" = ApiKey]
                    ]
                )
            ),
            Data = Table.FromList(Source[data], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            Expanded = Table.ExpandRecordColumn(Data, "Column1", {"period_end", "revenue", "net_income"})
        in
            Expanded,

    AllData = Table.Combine(List.Transform(Tickers, FetchTicker))
in
    AllData

Schedule Automatic Refresh

In Excel: Data → Queries & Connections → right-click your query → Properties → Usage tab → enable 'Refresh every N minutes' or 'Refresh data when opening the file'. When Apple files its next 10-Q, your model will update automatically on the next refresh.

📸

Excel Query Properties dialog: Usage tab → Refresh options

Screenshot coming soon

Need your API key first?

Free tier includes 10 tickers. Enough to build and test your Excel model.

Get Free API Key