BERDATA FOR EXCEL / HELP CENTRE

Your guide to BERdata.

From your first installation to your next refresh.
Find the steps, formulas and answers you need.

01

Install on your computer

Use current Microsoft 365 desktop Excel with custom functions and shared runtime support, internet access and your own BER API key. These packages install BERdata for your user. If your organisation manages add-ins, check with IT.

Windows

  1. Download the Windows package and extract the whole ZIP.
  2. Close Excel, including every open workbook.
  3. Open Install.cmd from the extracted folder.
  4. The installer registers BERdata and opens the starter workbook. Continue to connect your key.

No administrator elevation is requested.

Mac

  1. Download the Mac package and extract the whole ZIP.
  2. Quit Excel completely with Command-Q.
  3. Open Install.command from the extracted folder.
  4. The installer registers BERdata and opens the starter workbook. Continue to connect your key.

If macOS blocks the installer, see below.

Mac: the installer can’t be verified

If you downloaded the package from the BER site and macOS cannot verify Install.command, dismiss the warning. Open System Settings → Privacy & Security, find the message about that file, choose Open Anyway, then confirm Open.

This permits that file only. If the warning says the file is damaged or will damage your computer, stop and contact support. Apple’s instructions

Mac: install the manifest manually

If you prefer to copy the manifest yourself:

  1. Quit Excel with Command-Q.
  2. In Finder, choose Go → Go to Folder and open the folder below. Create it if it does not exist.
~/Library/Containers/com.microsoft.Excel/Data/Documents/wef/

Copy manifest.xml from the extracted download into that folder, using this filename:

1faa3c9e-9caf-4b88-a47e-8f83f462827e.manifest.xml

Open the included BERdata.xlsx workbook, or choose Home → Add-ins → BERdata in Excel. Microsoft’s Mac instructions

02

Connect your API key

  1. On Excel’s Home ribbon, open BERdata Settings.
  2. Enter your BER API key and choose Save key. Wait for the confirmation.
  3. Choose Test connection to check access.
Save first, then test.

Testing a key does not save it. Formulas use the key you’ve saved in Excel. Need a key? Contact BER.

You can also preview data in your browser. The browser has its own saved key for that tab; it does not set the key used by Excel.

03

Start with a formula

Enter a formula in an empty cell with space below and to the right. BERdata fills the surrounding cells with metadata and observations. You can request up to 20 unique series codes per call.

GETDATA

Dates, values and metadata

In cell A1, enter:

=BERDATA.GETDATA("P0141-CPS00000")

To limit the history, put a start date last. Omit it to return all observations:

=BERDATA.GETDATA("KBP6006D","1996-01-01")

The result includes metadata at the top, Time series code, Description, Category, Unit, Source, Frequency and Classification in rows 1–7, a blank row 8, column headings on row 9, and dated observations from row 10. On supported Excel versions, observation dates are numeric Excel dates and the add-in applies a date cell format automatically. The default display is yyyy-mm-dd; Excel may label it Custom. Dates are formatted separately from metadata and series values.

For several series side by side:

=BERDATA.GETDATA("P0141-CPS00000", "KBP7008Q")
GETVALUES

Values beside existing dates

After placing GETDATA in A1, enter this in D1:

=BERDATA.GETVALUES("P0141-CPS00000",A10)

Click the first observation date in the GETDATA table (A10 here) as the final argument. Start GETVALUES beside the top metadata row, not beside the first observation. The add-in identifies that table and matches values to its exact dates, keeping missing dates blank.

Build with your highest-frequency series first.

Quarterly values can be added to a monthly table, with blanks in months that have no quarterly observation. Monthly values cannot be added to a quarterly table: the formula returns #VALUE! and displays “Build your table with the higher frequency first.” in the cell below. No values are interpolated, aggregated or shifted into another date.

A typed date or an omitted date can use a single GETDATA table to the left on the same formula row. If there are several tables, click the first date to select one. Old date-first calls remain recognised. Unknown frequencies return a check error. Browser preview does not perform worksheet matching.

Use GETDATA to build a complete table, and GETVALUES when you want to add values to an existing one.

04

Keep your data current

Open BERdata Settings in Excel and choose the refresh that fits your work:

Refresh this sheet
Recalculates the used cells on the active sheet.
Refresh whole workbook
Requests a full rebuild across all open workbooks. Use this for named ranges, table references or calculated code expressions.
Refresh specific codes
Enter comma-separated series codes. BERdata searches the workbook for matching formulas, including codes supplied through supported cell references.

A refresh message confirms that recalculation was requested. Data requests may still be running. Workbook refresh controls are available in Excel; use Fetch preview for fresh data in the browser.

05

Find a quick fix

Start with the symptom you’re seeing.

I can’t find BERdata in Excel

Close Excel completely and rerun the installer from the extracted folder. On Mac, use Command-Q to quit. Reopen the included starter workbook and look for BERdata Settings on the Home ribbon.

If your organisation blocks manual add-ins or scripts, ask IT. If an old version still appears, check the upgrade guidance and contact support before clearing the Office cache.

My formula says no API key is saved

Open BERdata Settings inside Excel, enter your key and select Save key. Testing the connection alone does not save it. A key saved in the browser preview is separate from the Excel key.

The connection test or data request fails

Check your internet connection and the key you entered. The connection test requests P0141-CPS00000, so your key must have access to that series.

  • 401: check the key’s validity.
  • 404: a requested series may be missing or outside your key’s access.
  • 429: wait before trying again; the key’s request limit may have been reached.
  • 502 or 504: the data service could not complete the request. Try again later.

If it keeps failing, contact BER with the error message and series code. Do not include your API key.

Excel shows a spill error

GETDATA and GETVALUES expand into neighbouring cells. Clear or move cells blocking the result, or move the formula to an empty area with enough space below and to the right.

Refresh by code doesn’t find my formula

Use Refresh whole workbook if the series code comes from a named range, table reference or calculated expression. Refresh by code supports literal codes and supported A1 cell/range references.

GETVALUES doesn’t line up with my dates

Click the first observation date in your GETDATA table as the final argument. Place GETVALUES beside its top metadata row. Missing observations stay blank; if the new data has a higher frequency, rebuild the base table with that frequency first. See the D1 / A10 example. When in doubt, use GETDATA to return the series with its own dates.

06

Switching from a test version

This production version uses BERDATA. If an older workbook uses BERDATATEST, save a copy, replace BERDATATEST. with BERDATA. in its formulas, and check the results. Save your key again in the production add-in.

Remove BERdata Local Test before loading this version: it uses the same formula names under a different add-in ID. Use the old test version’s uninstaller to remove its registration and stale cache. The later Netlify beta also uses BERDATA; remove that registration before installing this BER-hosted version.

The BERLOCAL preview uses a separate namespace and key store. For a clean production check, remove it with its own uninstaller while Excel is closed; save a copy before changing BERLOCAL. formulas to BERDATA. This release retains the existing production add-in ID. Avoid duplicate manifest registrations. If Excel continues to load an old version, quit Excel and contact support before clearing its cache.

07

Your key and your data

Saved in Excel

Your key stays in the add-in’s shared storage until you select Clear saved key. Clearing it does not delete values already in your workbook or revoke the API key.

Separate in the browser

Browser preview keeps a separate key for the current tab. Closing the tab clears that copy.

Sent securely to BER

Requests send your key and selected series codes over HTTPS to BER’s data service. Your key controls access to series, and BER’s API supports usage logging.

Where do data requests go?

The add-in calls the BER relay at excel.beranalytics.co.za/api/getdata, which requests data from api.beranalytics.co.za. No API key is included in the download packages. First-party analytics record website visits, installer clicks, add-in opening, formula and refresh activity, runtime platform and version. Authenticated activity uses your API key’s internal registry ID, never the key itself. TEST usage is grouped as demo activity. No workbook contents or entered form values are collected.

08

Remove BERdata

Close Excel completely. From your extracted download, run Uninstall.cmd on Windows or Uninstall.command on Mac. Your workbooks are retained.

The uninstallers remove known manual BERdata registrations and identified BERdata cache entries, with a recovery backup. On Windows, keep BERdata-Cleanup.ps1 beside Uninstall.cmd. You can preview cleanup with Uninstall.cmd -Preview on Windows or ./Uninstall.command -Preview on Mac. They leave shared Office browser storage and personal workbooks alone. Ask IT to remove organisation-managed or store installations.

WE’RE HERE TO HELP

Still need a hand?

Tell us your operating system, Excel version, formula and the error you see. Keep your API key private.

Contact BER