Rates in Excel or Power BI

Connect Excel or Power BI to your runs once, then press Refresh to see the newest rates. You paste one query and never write code.

At a glance

Steps

1. Make sure a run has completed

   The query reads runs, it doesn't start them. Someone, or a scheduled job, needs to start runs with POST /v1/runs and a reference such as client-42, and at least one of them must have completed. The quick start shows how, and rates into your data warehouse shows a job that does it on a schedule.

   Runs started in the Beacon Pro app, or with POST /v1/reports/{id}/runs, have no reference, so this query can't read them.

   To see the columns before you connect anything, open the sample rates CSV in Excel. It has exactly the shape the query below returns.

2. Create a read key

   In Beacon Pro, open your initials in the top right, choose Company, then the API tab. Create a key with the Read scope. A read key can't start runs or spend credits, which is what you want in a file other people may open.

   Choose All company runs as its visibility. A key set to Its own runs only sees runs it started itself, and a read key never starts any, so it would see nothing. See API keys.

3. Add the key as a parameter

   Keeping the key in a parameter means it sits in one place, and you can change it without touching the query.

   • Excel: choose Data, Get Data, Launch Power Query Editor, then Manage Parameters, New Parameter.
   • Power BI Desktop: choose Home, Transform data, then Manage parameters, New parameter.

   Name it BeaconReadKey, set its type to Text, and paste your key as its current value.

4. Add the rates query

   In the Power Query Editor, choose New Source, then Blank Query. Open Advanced Editor, replace everything in it with the query below, and change client-42 to your reference.

   let
       // One rate per property and arrival date, from the newest completed run with this reference
       Source = Csv.Document(
           Web.Contents(
               "https://api.pro.getbeacon.cloud/lighthouse/v3/rates",
               [
                   Query = [subscriptionId = "client-42", format = "csv"],
                   Headers = [#"X-Oi-Authorization" = BeaconReadKey]
               ]
           ),
           [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
       ),
       Rates = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
       // Empty cells mean "no value"
       Blanks = Table.ReplaceValue(Rates, "", null, Replacer.ReplaceValue, Table.ColumnNames(Rates)),
       Typed = Table.TransformColumnTypes(
           Blanks,
           {
               {"HotelID", Int64.Type}, {"HotelName", type text},
               {"ExtractDateTime", type datetime}, {"ArrivalDate", type date},
               {"Los", Int64.Type}, {"Value", type number}, {"Currency", type text},
               {"Cancellable", type logical}, {"Best_Flex", type logical},
               {"RoomName", type text}, {"RoomType", type text},
               {"MaxPersons", Int64.Type}, {"MealTypeIncluded", Int64.Type},
               {"Message", type text}
           },
           "en-GB"
       )
   in
       Typed

   This reads the Lighthouse-compatible rates endpoint as CSV. Your reference is its subscriptionId. The key goes in X-Oi-Authorization, the header Lighthouse clients already send. You get one row per property and arrival date. Value holds the cheapest rate that passes the filters, or is empty with the reason in Message.

   To change what counts as "the rate", add filters to the Query record. Every value is text, in quotes:

   • persons = "3" for rooms that take at least 3 guests (the default is 2).
   • bar = "true" for the public flexible rate only.
   • mealType takes Lighthouse's meal codes: "1" for breakfast, "2" half board, "3" full board, "4" all inclusive, or "5" for room only. Each keeps only rates of exactly that meal plan.
   • los = "2" if your runs capture 2-night stays. See what to watch for.

   Every filter is listed in Switching from Lighthouse.

   If you load the export file or the native rates instead, the meal plan has its own column: Meal plan in the export (first in the rate plan group) and mealType in the rates, as a name such as half_board.

   Prefer the From Web dialog?

   Get Data, From Web, Advanced does the same job. Put the address with ?subscriptionId=client-42&format=csv in the URL parts, and add X-Oi-Authorization under the request headers. The dialog stores the key inside the query. The version above reads it from the parameter, which is easier to look after.

5. Connect with Anonymous

   The first time the query runs, Power Query asks how to connect to api.pro.getbeacon.cloud. Choose Anonymous and Connect. The key already travels in the header, so there's no user name or password to enter.

   If Power Query asks about privacy levels, set this address to Organizational.

   If you see an error instead of rows, check the key: a wrong or revoked key answers 401, and a key limited to certain IP addresses answers 403 from anywhere else, including Power BI's online refresh. See if a request is refused.

6. Load it and refresh

   Choose Close & Load in Excel, or Close & Apply in Power BI. From then on, Refresh fetches the rates again.

   The query always reads the newest completed run with your reference, so when a new run with that reference completes, the next refresh shows it. You don't change anything in the workbook. Until then, a refresh shows the same rates again.

7. Optional: the native rates as JSON

   The Rate Capture API's own rates endpoint returns the same choice of rate with its own field names, such as checkIn, price and freeCancellation. It reads one run by its id, so this query first finds the newest completed run with your reference, then reads its rates.

   let
       Api = "https://api.pro.getbeacon.cloud/v1",
       Auth = [Authorization = "Bearer " & BeaconReadKey],
       // The newest completed run with this reference
       Runs = Json.Document(
           Web.Contents(Api, [
               RelativePath = "runs",
               Query = [reference = "client-42", status = "completed", limit = "1"],
               Headers = Auth
           ])
       ),
       RunId = Number.ToText(Runs[runs]{0}[id]),
       // One rate per property and check-in date from that run
       Response = Json.Document(
           Web.Contents(Api, [
               RelativePath = "runs/" & RunId & "/rates",
               Query = [persons = "2"],
               Headers = Auth
           ])
       ),
       Rates = Table.FromRecords(Response[rates]),
       Typed = Table.TransformColumnTypes(
           Rates,
           {
               {"hotelId", Int64.Type}, {"propertyId", Int64.Type}, {"hotelName", type text},
               {"checkIn", type date}, {"fetchedAt", type datetimezone}, {"nights", Int64.Type},
               {"currency", type text}, {"price", type number}, {"originalPrice", type number},
               {"roomName", type text}, {"roomType", type text}, {"maxOccupancy", Int64.Type},
               {"breakfastIncluded", type logical}, {"mealType", type text},
               {"freeCancellation", type logical},
               {"nonRefundable", type logical}, {"bestFlex", type logical}, {"message", type text},
               {"cancellationDeadline", type date}, {"cancellationDeadlineTime", type text},
               {"flexibility", type text}, {"reschedulable", type logical}
           }
       )
   in
       Typed

   The answer is a JSON object with the run, the filters applied and a rates list. Table.FromRecords turns each rate in that list into a row. The same filters work here, plus perRoomType = "1" for one rate per room type. See rates.

   Choose Anonymous for this address too. If no run with your reference has completed yet, the query stops with an error at RunId.

What to watch for

• No rows yet. Until the first run with your reference completes, the CSV has only its header line. Check the reference is spelt exactly as it was sent.
• Stay length. los defaults to 1 night. If your runs capture a longer stay, add los with that number of nights, or every date answers general.missing.
• Other sites. The rates use the ota filter, which defaults to bookingdotcom. A property on another site gives general.missing.
• Empty values are reasons, not zeros. When a date has no rate, Value is empty and Message says why, for example rates.soldout or rates.restrictionlos4 (the property needs a stay of at least 4 nights from that date). Don't count those as a price of 0.
• Prices are for the whole stay, in the run's currency. Whether a price includes tax isn't captured, so the tax columns are always empty.
• Deleted rows. If a run's rows are deleted, by someone after loading them elsewhere or by your company's retention period, the CSV query reads the newest earlier run with your reference whose rows still exist. The JSON query doesn't skip it and stops with 410 instead.
• Character set. The CSV is UTF-8 (text/csv; charset=utf-8), which is why the query reads it with Encoding = 65001. Property names with accents come through as they are.
• Names that start with a symbol. In the CSV, a text cell that starts with =, +, - or @ has a ' in front, so a spreadsheet never runs it as a formula.
• The key is saved in the file. Anyone who can open the workbook or report can read the parameter. Use a read key, and revoke it if the file goes somewhere it shouldn't.