The distributor sells on four major channels: Shopify, Amazon, Etsy, Walmart. All four want their own pricing logic — each has its own set of fees, fulfillment rates, payment processing, its own penalties for different categories and dimensions. On top of that the brand enforces an RRP with an allowed deviation band.

Before our work the manager did this:

• Download a template → export → recompute in Excel → upload. 1–2 hours per channel. Four channels — a whole day.
• Fees changed — formulas went stale. A day after updating, it would turn out some items had slipped into the red.
• RRP — in a separate file owned by someone else. To answer "can we set price X?" you had to gather data from three places.
• Channels updated on different days. Not by design, but because the manager ran out of time. The result — inconsistent prices, unhappy customers, RRP conflicts.

Architecture

The crucial point: no manual template downloads from back-offices. The M code pulls everything it needs over the API in one pass. Excel is where the business logic lives, not where copy-paste lives.

Channel APIs in M code

Power Query in M can make HTTP requests. Each channel has its own function: pull the token from the hidden sheet, do a GET/POST, parse the JSON, shape it into a table. All four functions are structurally similar; the shared wrapper:

// fnCh_Fees — shared fee-request function
// Takes: channel key, endpoint, params
let
    Creds = Excel.CurrentWorkbook(){[Name="API_Tokens"]}[Content],

    fnCh = (channel as text, endpoint as text, params as record) =>
        let
            token = Table.SelectRows(Creds, each [Channel]=channel){0}[Token],
            URL   = "https://api." & channel & ".../" & endpoint,
            resp  = Json.Document(
                Web.Contents(URL, [
                    Headers = [#"Authorization" = "Bearer " & token,
                               #"Content-Type"  = "application/json"],
                    Query   = params
                ])),
            table = Table.FromRecords(resp[items])
        in
            table
in
    fnCh

On top — per-channel wrappers that know their endpoints:

// Shopify_Fees — current rates per listing
let
    Source = fnCh("shopify", "v1/fees", [channelType = "online"]),
    Expand = Table.ExpandRecordColumn(Source, "fee",
                  {"transaction", "payment", "fulfillment"}),
    Types  = Table.TransformColumnTypes(Expand,
            {{"transaction", type number}, {"payment", type number}})
in
    Types

On the "Auto-prices" sheet every SKU automatically gets fresh fees — without copying tables out of a back-office.

Google Sheets — the single source of base prices

The same Google Sheet that holds the purchasing parameters (lead time, thresholds) also holds the pricing parameters. One sheet — both systems. Convenient, because the buyer and the pricing manager see the same thing.

Power Query pivots the "long" format into "wide" — each SKU becomes a row with RRP, Wholesale, Cost and so on as columns. That feeds the Excel target-price formulas.

The target-price formula

The basic idea: the target price must cover all costs and leave the target margin without breaking the RRP band. All components are assembled on the sheet:

As a real Excel formula:

=([@Cost]
  + [@[Logistics, $]]
  + [@[Payment, $]]
  + [@[Target margin, $]]
 ) / (1 - [@[Channel fee]])

The result is checked against the RRP band:

=IF([@[Target price]] < [@RRP] * (1 - [@[Allowed deviation]]),
       "BELOW RRP",
       IF([@[Target price]] > [@RRP],
            "ABOVE RRP",
            "IN BAND"))

The manager walks the "BELOW RRP" / "ABOVE RRP" items visually and decides what to do with them (raise the minimum allowed, change the margin, or leave it out of band deliberately).

A one-shot update of 4 channels

On the "Auto-prices" sheet each channel has its own section with its own fee column and its own output template:

When the manager has finished the visual check, the four "Export_*" sheets are already ready to copy. Each one is formatted strictly for upload into the matching back-office. Prices change on all four channels in sync, on the same day.