Excel add-in and Power Query

TS 2.12. MacroLens is linked to the Microsoft Excel application running on Windows 11. There are two ways to get data into Excel:

In addition, you can simply download any table as XLSX.

The complete installation kit (the add-in manifest file, Power Query templates and links) is on the MacroLens Excel page.

Step 1. Signing in

The add-in: click Sign in with my MacroLens account in the MacroLens pane. A MacroLens sign-in window opens — enter your e-mail address, password and the two-factor (MFA) code exactly as in the web application. After Connect the window closes and the pane shows your name. The add-in then holds a personal access token for this Excel installation only (valid for 90 days and extended while in use); it is kept on this computer, never in the workbook. Sign out in the pane revokes it immediately.

Power Query (and the add-in's alternative Sign in with a personal access token, e.g. when the sign-in window cannot open) uses a personal access token that you create yourself:

1. On the MacroLens Excel page (or in My account → API access tokens) create a new token (e.g. named “Excel – office PC”) and choose how long it is valid.
2. The token is shown only once — copy it and paste it into Power Query (or the add-in). Do not store the token in shared places and do not pass it on.
3. You can revoke a token at any time in My account → API access tokens (e.g. when you change computers). A revoked or expired token stops working — then create a new one.

A token gives the same access as your account, so protect it like a password.

Step 2. Using the add-in

Once the add-in is installed, a MacroLens button appears on the Excel Home tab (if not, use Home → Add-ins and choose MacroLens).

1. Sign in. Click Sign in with my MacroLens account and sign in in the MacroLens window (e-mail, password and two-factor code — see Step 1). The sign-in is remembered on this computer until you sign out or the token is revoked.
2. Search. Enter keywords or a phrase and choose filters if needed (source, region, frequency, category). The syntax is the same as in the MacroLens search. Results show the title, source, frequency, unit, period and the date of the last update.
3. Insert. Tick the series you need, choose the period and other options, select a cell in the workbook and insert. Values are written as numbers together with the series metadata (title, key, unit, source). If the selected series have different frequencies (e.g. monthly, quarterly and annual) and the frequency is left at Original frequency, the add-in asks how to insert them: one table per frequency (placed side by side; the default) or all series converted to the lowest frequency — rows of different frequencies are never mixed in one table. Daily series get rows only for the days with observations (no empty weekend rows), and Last N periods counts those days.
4. Refresh. The add-in remembers which data was inserted from MacroLens. Refresh the selected block or all blocks in the workbook — the latest values are written, including new periods and revisions.

The workbook stays an ordinary .xlsx file without macros. Colleagues without the add-in see the last values written.

Power Query

Power Query lets you create a refreshable data query without installing anything:

1. On the MacroLens Excel page, download a prepared refreshable workbook or copy the data link.
2. To build the query yourself: in Excel choose Data → Get Data → From Other Sources → From Web and paste the link.
3. When Excel asks for credentials, choose Basic authentication (not Anonymous, Windows or Organizational account): user name — your MacroLens e-mail address, password — your personal access token (see Step 1); set the level to the MacroLens address https://macrolens.sixtyfour.ee. Power Query cannot show the two-factor sign-in window, which is why it uses a token.
4. Refresh the data with Data → Refresh All. In the query properties you can set automatic refresh when the file is opened.

Credentials are stored in the Power Query settings of your computer, not in the file, so the file can be sent to colleagues — each signs in with their own token. To change the credentials: Data → Get Data → Data Source Settings.

The data sources' licences require their attribution with every copy of the data. The JSON feed carries it for every series (series[].attribution); the refreshable workbook shows it on a separate sheet “Sources”. The CSV variant (data.csv) has it in its last column, “Source and licence” — one notice per row from the first data row on (the ECB's “may be obtained free of charge” notice, the Eurostat citation, “Calculated by MacroLens from …” for transformed data …). It is an ordinary text column, so the value columns and the rows of the table stay clean; keep these notices when you publish or pass on the data.

Information for IT administrators

Requirements:

• Microsoft Excel on a Windows 11 PC: Microsoft 365 Apps (any supported channel) or Office LTSC 2024 / 2021. If your organisation uses another version, we will verify compatibility together with you.
• Network: HTTPS (port 443) access to macrolens.sixtyfour.ee and to Microsoft's Office.js library at appsforoffice.microsoft.com.
• The add-in is an Office web add-in: no executable code is installed on the computers, no code-signing certificates are needed, and add-in updates reach users automatically.

Deployment options (download the add-in manifest file from the Excel page):

1. Recommended — centralised deployment: Microsoft 365 admin center → Settings → Integrated apps → Upload custom apps → choose “Office Add-in” and upload the manifest file → assign users or a group → Deploy. The add-in appears for users within a few hours (up to 24 hours); users need to restart Excel.
2. SharePoint app catalog — upload the manifest to the organisation's “Apps for Office” catalog; users add the add-in via Home → Add-ins → My Organization.
3. Shared network folder (trusted catalog) — the manifest is placed in a network folder that group policy registers as a trusted add-in catalog.
4. Excel on the web — for testing: Home → Add-ins → More Settings → Upload My Add-in and choose the manifest file.

Metadata sheet: the add-in describes the inserted blocks on a sheet MacroLens_info, which is very hidden. In volume-licensed Office 2016/2019 (basic mode, ExcelApi 1.1) Excel does not let add-ins hide sheets, so there the sheet stays visible — leave it unchanged, the add-in needs it to refresh the blocks.

Removal: for centralised deployment remove the MacroLens deployment in “Integrated apps”. Users' access tokens are revoked in their MacroLens account (My account → API access tokens); Sign out in the add-in revokes the add-in's token.

Troubleshooting

If the problem persists, contact support — state your Excel version (File → Account → About Excel).