How to share Power BI reports

In this blog post, we will learn how to share Power BI reports with others. After all, it is important to share the insights from your reports with others. It is an admittedly tricky topic with several rules and exceptions, which we will navigate through.

The Power BI license essentials

To begin with, in Power BI, there are a few different licenses available to the users. The licenses are:

• Free
• Pro
• Premium Per User

To begin with, the free version can be used to build the actual report, but there is no possibility to share a live version of it. However, you can still take snippets or export it to PDF for further distribution. Also, you can share the actual Power BI file, called PBIX, with other users. These users can then install Power BI Desktop and open the PBIX file. This can be achieved all for free, but these are not “live” sharing methods, however.

A more convenient option, is for the report creator to pay for the Pro license. By doing this, the report can be shared with everyone, fully open at Internet. This way, it can also be embedded to websites, just like we have done in our Portfolio. In Power BI, this option is called “Publish to web”. Keep in mind, that a link will be generated if using this method, and the link is accessible to anyone. Also, by paying for the Pro license, the creator can set up automated data refreshing which is a useful feature.

If we imagine that the report is too sensitive to be shared with everyone, the end user of the report will need to pay for a license. The end user will have to purchase either a Pro or Premium license, and this applies to each end user. For example, if a business has five employees who all should have access to the report, five licenses are required.

The creator can also purchase a Premium Per User (PPU) license. It is essentially the same as the Pro license but offers more advanced capabilities like AI, refreshing each report 48 times per day, more memory consumed by the Power BI report etc.

Prices for the licenses

At the time of writing this post – February 2024 – the prices are:

• Free: $0
• Pro: $10 per user per month
• Premium Per User: $20 per user per month

Capacity license

Above, we have discussed the licensing question from a creator and end user perspective. There is also a licensing question to be discussed for the so called “Power BI capacity”. This is essentially a shared destination where reports reside and can be thought of as a server, but it is in the cloud. Now, if a business purchases a Premium capacity, it changes the answers to what we discussed above.

The largest benefit of having the Premium capacity, is that it is possible to share with all users that you want to share the report with, and there is no need for the end users to have any license at all. There are some other benefits as well, for example the business can take control of the reporting and own it themselves. If a Premium capacity is not purchased, the creator and end user will need to have what is called a Shared capacity, hence the creator has access to the report also after publication. There are ways to solve that, for example the end user can revoke the access for the creator.

Admittedly, the Premium capacity is a costly solution, and currently it starts at $4,995 per month. That is approximately 500 Power BI Pro licenses, so that is about how many users are needed for this solution to make sense. However, administration of these 500 Pro licenses will also be demanding, so we will argue that the Premium capacity is useful before having that many users. It also enables nicer solutions, but there is certainly a minimum number of users before it really makes sense to buy it.

Considerations on how to share Power BI reports

In mathematics, there is the concept of necessity and sufficiency (link for more background). For example, it is necessary for a team to be qualified for the World Cup in order to win the championships. It is not sufficient, however, just to be qualified. To win the World Cup, it is necessary to win the final. By definition, the winner of the final is the World champion, so it is also sufficient.

If you are a business or private person who just wants to display non-sensitive data to the rest of the world, in the form of a live Power BI report, a Pro or Premium license can be sufficient. It is also necessary to have a Pro or Premium license to be able to share the report. As soon as it is sensitive information, however, having a Pro or Premium license only for the creator, is not sufficient. It is still necessary, however, to have at least a Pro license for the creator. As we can see, there are many scenarios to consider. The table below summarises in which scenarios the report can be shared in case there is no Premium capacity, that is, they are using a shared capacity.

In the table above, * means that in the case of the creator having a Pro/Premium license, it is possible to “Publish to web”, meaning that it will be freely accessible at Internet. For businesses, this is typically not an option, hence this is categorised as No, but we still mention this as an exception. The table below summarises in which scenarios the report can be shared in case there is a Premium capacity.

Summary

We have learnt what is needed from the creator’s and the end users’ perspectives in order to share Power BI reports. To summarise, for small businesses, at least a Power BI Pro license is needed for the creator and likely also for each end user. For large businesses, the same setup can be valid, but they should also look into having a Power BI Premium capacity, which is a bit costly but which drastically reduces the number of licenses needed. We also learnt some workarounds on how to share Power BI reports, for example to share the PBIX file between users.

On the development of the NOK exchange rates in January 2024

In this blog post, we will analyse the development of the Norwegian krone (NOK) exchange rates in January 2024. We will use a Power BI report that is available in our Portfolio.

The source of all data is the Norwegian central bank – Norges Bank.

January 2024 overview

We start by going to this page, and change the year filter to 2024. We then expand the table and click on January. Doing so, the rest of the page will update with January as a filter. This is a convenient way of filtering a Power BI page without having actual filters visually present in the report.

We then see that NOK strengthened against three currencies – Australian dollar (AUD), Japanese yen (JPY) and Swedish krona (SEK). Similarly to what we reported for SEK in our blog post, there were no dramatic changes in January. All movements were approximately within the range -2% to +2%. It can be noted here that + means that NOK weakened, and – means that NOK strengthened. For example, if the EUR-NOK exchange rate goes from 10 to 11, that +1 change is a weakening of the NOK.

We note that NOK weakened the most – by 2.0% – against Pound sterling (GBP) and then by 1.7% against the US Dollar (USD). NOK strengthened the most – by 2.2% – against JPY.

Zooming in on the chart of percentual exchange rate development, it will look like below. We see that most of the development happened before approximately 25th January, and after that it was relatively flat.

If we compare the NOK development to large currencies like Euro (EUR), GBP, JPY and USD, it will look like below. We see that NOK weakened against all of them except for JPY.

On the other hand, if we compare NOK development to smaller currencies like Brazilian real (BRL) and SEK, it will look like below.

Combining the chart of large currencies with this chart of smaller currencies, gives no clear correlation of NOK exchange rate development depending on the popularity of the currency. It can still be an interesting perspective, however, for example to know if NOK is catching up with large currencies or not.

Finally, we go to this page and review a snapshot taken at the 1st February 2024. The exchange rate development, we have discussed above, but looking at this picture, we get more insights on the NOK bond yields development. For example, one could then see if there is a relation between rate increases and stronger NOK.

We can see in the picture above that the longer-term NOK government bond yields increased more than the shorter-term bond yields. For example, the 10 years bond yield increased by 27 bps – to 3.52%, but the 3 months bond yield only increased by 1 bps – to 4.52%. We also realise that the 3 months bond yield is 100 bps higher than the 10 years yield.

We also look at the bond spreads, which are the differences in yield between shorter-term bonds and longer-term bonds. For example, a common financial metric is to measure the difference in yield between a 5 years bond and a 10 years bond. This is also called “5s10s” and that bond spread is now at 0.13%. This means that the yield of the 10 years bond is 13 bps higher than the 5 years bond. This is normal and usually investors require higher yield for longer duration as the money are “locked” for a longer time period. We see that the 5s10s bond spread increased by 11 bps in January. Since both of the bonds increased, it means that the 10 years bond yield increased more than the 5 years bond yield.

Summary

In January 2024, there were no dramatic changes of the NOK exchange rates compared to major currencies like GBP, EUR, JPY and USD. NOK weakened by 2.0% against GBP and by 1.7% against USD, while it strengthened by 2.2% against JPY. This is very similar to the SEK development as we described in a previous post.

With regards to bond yields, the NOK bonds with longer duration increased more than the ones with shorter duration. For example, the 10 years bond yield increased by 27 bps while the 3 months bond yield only increased by 1 bps.

Analysing website performance using Power BI and Google Analytics

In this blog post, we will analyse website performance using Power BI and Google Analytics (GA4) data. We do so by using an existing report, which is available in our Portfolio, and it is called Website statistics. For information, we described in a previous blog post how to connect Power BI and Google Analytics.

Introduction of website analytics using Power BI and Google Analytics

The report connects Power BI and Google Analytics, and we are using the GA4 connector to do so. It is a rather simple report with just one page, and the purpose is to show effectively a snapshot of the most important website metrics. Using the Power BI online service, we have set up automatic refreshing of the GA4 data. For more information about Google Analytics, we refer to this page.

The default view presents the performance in the Current month, but it is possible to go back in time to review the month of interest. We measure the following metrics:

• Sessions
• Bounce rate
• Hits
• Visitor type
• Visitor source
• Seconds spent per page

They are visualised from different perspectives, for example, the sessions can be seen per day and per city and they are also compared to the last month.

Quick introduction of the measured metrics

We will here go through the interpretation of the metrics used in the report and justify why they are measured.

Sessions is the most basic website metric, and basically measures all traffic coming to your website. It is the starting point of all website analytics.

Bounce rate means that only one page is visited for a specific session. Typically, it is desired to keep the visitor engaged and have the visitor visit multiple of your pages. We measure this because we want to ensure our full website content is shown to the visitor.

Hits measures the number of user interactions. For example, a user clicking on the link to another page would imply one hit. Similarly to bounce rate, we want to ensure that our content and message are displayed to the visitor.

Visitor type measures if it is a New user or an Existing user that is coming back to your site. This is important to measure, because it needs to be aligned with your business intentions. For example, if you want to attract new customers, you would like to see New users visiting the site.

Visitor sources measures from where the traffic originates. For example, direct means that the visitor knows your URL and has visited your site directly. This is an important metric as it shows how well your marketing campaigns and marketing outreach are working.

Analysis

In this chart, we see the number of sessions for each day. With the line, we are also able to see the accumulative number of sessions throughout the month. For example, we see that approximately from day 11 to day 20, there was no traffic at all.

We present three high-level KPIs and compare them to the last month. The first KPI is the number of sessions which were up by 76%, compared to the last month, which is a large change. Then there is the bounce rate, which means that in the month, on average, 27% of the sessions only had one page visited. Ideally, here we would like to see a low %, as we would like the user to visit several pages. Then, we see with the hits, that on average 0.3 user interactions happened per session. This was an increase by 8% compared to the last month.

If we then move on to more in-depth KPIs, we see a distribution of the sessions by the city they originated from.

When reviewing where visitors spend their time, we see that the Portfolio page was, by far, the page that the visitors spent the most time at.

Next, we see that 57% were New users and 43% were Returning users. We consider this a healthy balance, but it also is very specific for each site’s business intentions. We also see that we had two types of traffic sources – direct and via Google, where direct traffic dominated at 67% of all traffic.

Summary

We used a Power BI report to analyse website performance. In addition, we looked at common website metrics like sessions, visitor types, visitor sources etc. and learnt why they are important to measure. By using Power BI and Google Analytics data together, we can tailor our analysis exactly how we want it, but there are also possibilities to use other tools, like the built-in charts in Google Analytics’ web tool.

On the development of the SEK exchange rates in January 2024

In this blog post, we will analyse the development of the SEK exchange rates in January 2024. We will use a Power BI report that is available in our Portfolio. In case you are interested in an analysis over a longer time period, we have previously written a blog post about that.

The source of all data is the Swedish central bank – the Riksbank.

January 2024 overview

We start by going to this page, and change the year filter to 2024. We then click on January in the table. A good feature with Power BI, is that it is dynamic and by clicking on January, the rest of the page updates with January as a filter.

We then see in the chart on the top left, that SEK only strengthened against one of the currencies – Japanese yen (JPY) – out of the ones present in the report. We also see that there were no dramatic changes in January, and all movements were approximately within the range -2% to +3%. It can be noted here that + means that SEK weakened, and – means that SEK strengthened. For example, if the EUR-SEK exchange rate goes from 10 to 11, that +1 change is a weakening of the SEK.

We note that SEK weakened the most against Pound sterling (GBP) at 2.4% and then with 2.1% against the US Dollar (USD). The SEK barely changed against CHF (+0.6%) and NOK (+0.4%).

Zooming in on the chart of percentual exchange rate development, it will look like below. We see that in the very last days of January, there was a strengthening of the SEK against all currencies.

If we review the movements of the SEK 5 and 10 years bond yields, and compare to the development of the same for USD respective GBP, we also see no dramatic movements that could explain the exchange rate development. In addition, we see that the SEK bond yields were all up in January, for example the 10 years yield went from 2.11% to 2.22%.

Finally, we dive a bit deeper in regards to the bond yields. Using the two pictures above, we see (in Power BI only) that the USD 5 years bond went from 3.92% to 3.84% (-8 bps) in January while the SEK 5 years bond went from 2.01% to 2.16% (+15 bps). Conversely, the GBP 5 years bond yield went from 3.55% to 3.74% (+19 bps), which is more in line with the SEK equivalent.

Summary

In January 2024, there were no dramatic changes of the SEK exchange rates compared to major currencies like GBP, EUR and USD. SEK weakened by 2.4% against GBP and by 2.1% against USD, while it strengthened by 1.8% against JPY.

With regards to bond yields, the SEK 5 years yield increased by 15 bps. That can be compared to the USD 5 years yield that decreased by 8 bps and the GBP equivalent which increased by 19 bps.

How to read data from Google Analytics to Power BI

In this blog post, we will learn how to connect Google Analytics to Power BI, using the latest version of Google Analytics called GA4. For information, we discussed in a previous blog post how to connect Power BI to a database using a REST API. Google Analytics uses another technology, however, which we will go through here. The post assumes that you have a Google account set up, and that you are tracking statistics for your website.

Introduction to Google Analytics

Google Analytics is a popular tool for analysing traffic on websites. More specifically, it is possible to track user behaviours on your website and learn more about how your content caters to your audience. For example, you can see how certain pages perform in terms of making the user stay at the page. You can then learn which type of content performs well. It is also possible to see from which country and city that your traffic originates from.

You can also see how much your users interact with your website. For example, if much of your traffic only arrives at the start page and then have no further interactions, it could indicate some problems with your website. It could also mean that it is very important that you optimise your message to be shown efficiently on that start page.

It is also possible to see if the traffic originates from new visitors or previous visitors. Again, this could impact how you want to position your content, what content you shall develop and it could infleunce your marketing strategies as well. For example, if your traffic only consists of previous visitors, it could indicate that you need to attract new visitors. Here is some further information on Google Analytics in general.

Introduction to Google Analytics in Power BI

In order to connect to all the data that exists in the Google Analytics environment, there is a special connector developed for the purpose. There are two versions of the connector – 1.0 and 2.0 (Beta). The 1.0 version is related to a previous Google Analytics version called Universal Analytics. In 2023, Google released its new version called Google Analytics 4 (GA4). The version called 2.0 (Beta) supports GA4, so we will use that in our guideline. For more information on the Power BI connector, please see Microsoft’s website.

Connecting Google Analytics to Power BI

We start by opening Power BI and go to Get data. It will look like this, and then the user selects “More…”.

Then we search for Google, select Google Analytics and click on Connect.

Next, we choose 2.0 (Beta) because we want to use the GA4 version of the connector.

We then are asked to sign in to our Google account in the web browser.

After having logged in, we are able to select from several KPIs and we can also select from which aspect we want to measure said KPIs. For example, we here select sessions which is one of the most basic measurements. Basically, sessions means all traffic (sessions) that have been appearing at your website. Next, we select that we want to measure sessions by date and country. This way, we will be able to see the traffic numbers for each day, and we can see how much of the traffic that originates from each country.

We click on Load, and the data will look like below. We can note that the data is shown in a summarised way. For example, it says Ireland and for a specific day, there were 20 sessions. This is a more efficient way than showing each session specifically. However, as we bring in more dimensions, we will get more columns and rows of data which will impact performance. For example, if we are to add city, perhaps 5 sessions are from a certain city, 10 are from another city etc. So it would change the data structure.

It can be a good idea to think through what you want to analyse and from what perspective. Depending on how much web traffic data that your site has, what kind of data you want to read will influence the performance of the Power BI report. The Google Analytics connector in Power BI also has a limit of 10 KPIs per data query. A way to get around that is to have multiple queries, but with each KPI and query added the Power BI performance will be impacted. If your site focuses on keeping existing visitors, your analysis can start with reading much data related to existing visitors in one query. You can then complement it with other KPIs in a second query, for example.

Summary

In this blog post, we discussed some benefits of using Google Analytics 4 (GA4) data to analyse your website performane. We also learnt how to connect Google Analytics to Power BI, by using the built-in connector. More specifically, we learnt how to use the connector for GA4, which is the most recent version of Google Analytics. Finally, we reviewed some data and discussed what GA4 data is relevant to analyse and some limitations of the GA4 connector.

A closer look at the Swiss tourism sector using Power BI for data analysis

In this blog post, we will look at Swiss tourism from a numerical perspective. We will use data that is provided by Switzerland Tourism. The data is read directly to Power BI by using their REST API, and the data is updated three times per day. Switzerland Tourism is an organisation that works with promoting tourism in Switzerland. On their website, they present information about Switzerland, and they also list current accommodation deals for example.

Introduction to the Power BI report

Using the REST API, we can read all of the accommodation deals instead of having to browse the site. We can then visualise it in our own ways and filter as we prefer. The API also offers the possibility to read information about Switzerland’s extensive possibilities for activities like skiing in the Alps, hiking, biking etc. We have a previous blog post which explains more on how to read data from a REST API to Power BI.

In our Portfolio, we have a report, called “Report of available offers”, which looks at all the current offers listed on Switzerland Tourism’s website. These offers are mostly accommodation deals, but they can also be one-day conference offers at reduced price as an example. We also have a page with information about the different activities, typically outdoor activities, that are found in Switzerland.

Using the report for analysis

Starting with the available offers, we have created a simple page, which is seen below, consisting of:

• Two filters – Price and Location
• One chart – When do the offers expire?
• Four numerical KPIs
• One map – To show where the offers can be found

According to the data read from Switzerland Tourism, there are 4 083 destinations, such as hotels, hostels, bed-and-breakfasts, etc. across Switzerland. We can see that there are 194 active offers listed at myswitzerland.com. We also see that there are 136 destinations, out of 4 083, that have at least one active offer. This means that there are some destinations that have multiple offers active at the same time. Dividing the 136 destinations that have an active offer by the total number of destinations – 4 083 – we see that 3% of the destinations have at least one active offer. There is also the possibility that there are more offers, offered only locally on hotels’ websites etc., but the numbers used here are the ones listed on Switzerland Tourism’s website.

We also see in the chart to the right that most offers expire during the spring in 2024. This is reasonable for two reasons. First, the skiing season in the spring time is quite popular in Switzerland so there will be many offers related to skiing. Second, it is common not to publish offers too far in advance, so mostly “near-term” offers will be listed.

We also offer the possibility to filter by price. For example, if we want to find offers below 150 CHF, the page will be updated and look like this. As can be seen in the picture below, there are 30 such offers currently available.

We can hover over the map and get some additional information for each destination, like is shown below.

Our report is updated three times per day, and we could have chosen to update it even more frequently. It is interesting to see how data is being used within the (Swiss) tourism sector, and we see a few interesting use cases. For example, a hotel could set up this data pipeline in order to monitor what their competitors are offering, respective not offering, and what are the current price levels.

Find offers by location – Luzern as an example

We can also filter by location. Here we filter in order to find available offers in Luzern. There are three offers available at the moment in Luzern.

Skiing, hiking and biking in Switzerland

We also read data over available tours in Switzerland, which are for example skiing, hiking and biking tours, but also categories like “City Breaker” and “Family” are available. In this post, we will analyse the data behind the hiking tours found in Switzerland. So, we start by using the filter Tour type and set it to “Outdoor Enthusiast – Hiker”.

Each tour comes with a location, name and description. This way, we are able to visualise on the map where it is located, and the user can also hover to read more information about the tour.

Each tour also has a certain length and ascent listed in the data stream. This way, we are able to visualise the different lengths and ascents. For example, the users might be looking for a tour length between 10 and 20 km. We can see in the chart above that there are 325 such tours.

Each tour is also classified with respect to the endurance level and technical level required. Typically, long tours with much ascent will be classified as Difficult both with respect to endurance and technical levels needed. Vice versa, a short tour with little ascent will typically be classified as Easy with respect to the endurance and technical levels needed. For example, if we filter by Difficult endurance level, the page will look like this.

We see that there are 303 difficult tours, and we note that the average distance is 35 km while the average ascent is 1 720 meters. A great way to use the dynamics of Power BI, is to click on a chart and then filter the rest of the page. Here, we filter by clicking on the 20 – 40 km distance range in the chart. We then see, in the picture below, that there are 120 tours in this range. The average distance as well as the average ascent changed – to 25 km and 902 m, respectively. The chart with the distribution of the ascents also updated, to visualise what are the ascents of these 120 tours.

Conversely, we can do the opposite. Suppose we want to find a tour with an ascent of at most 300 meters. We then see that there are only 19 such tours, which at the same time are at a Difficult endurance level.

Summary

In this post we have used a Power BI report to look at data related to the Swiss tourism sector. We also explored how the map visualisation can be filled with information in Power BI. In addition, we learnt how to dynamically filter a page by clicking on the data point of interest, in this case the Endurance and Ascent parameters. Finally, we hope that you find the report useful when planning your next trip to Switzerland.

Analysing the Swedish krona Short-Term Rate (SWESTR) development using Power BI

The Swedish krona Short-Term Rate (SWESTR) is a reference rate, meaning that it can be used as a reference for setting rates between banks. The SWESTR is completely based on transactions happening in the market and is published every bank day at Riksbanken’s website. It is intended to be used for loans with the shortest term, for example as a so called over-night rate (O/N rate) or tomorrow-next rate (T/N rate). For more information about SWESTR, please refer to this interview with a Riksbank representative.

Reading data to Power BI using a REST API

The SWESTR went live in September 2021. Data is published each bank day and contains for example the current SWESTR rate, the volume and number of transactions. The data can be read by utilising the Riksbank’s REST API. In a previous blog post, we guided readers on how to read SWESTR data to Power BI via the REST API. 

Our report, called Report of Swedish krona Short-Term Rate (SWESTR), reads the data directly to Power BI by using the REST API. Below is a picture of the report, available in our Portfolio, which we will now analyse further.

It can be seen that since the launch in Q3 2021, the rate has increased much. In order to reduce the noise of potentially low/high values, we present averages in our charts. For example the 1 week average rate, seen in the chart below, is the average rate for that week. The 1 week average, hence roughly also the SWESTR itself, has increased from -0.1% at the end of Q3 2021 to 3.9% at the end of Q4 2023. That is a dramatic increase of around 400 basis points (bps) in approximately 2 years.

We also present the latest published SWESTR rate – at the time of writing this, it is 3.898%. That is 150 bps higher than 1 year ago, which is also a quite dramatic increase. Similarly to the 1 week average, we also present the averages calculated over longer time periods (1 – 6 monhts), which can be seen in the chart below. Naturally, the 6 months average will be “slower” and have less noise. It can be seen that all the four averages converged at the end of 2023, which means that the SWESTR value has been flat for some time.

SWESTR performance metrics

The volume, in terms of SEK per day, and the number of transactions per day, are also presented as can be seen in the chart below.

It seems that the SWESTR is gaining in popularity. It started out in Q3 2021 with on average 42 transactions per day. The average volume per day was 36.6 billion SEK. On average, this gives approximately 870 million SEK per transaction. The Riksbank then considers all of these transactions and calculates the rate to be used the next day for new transactions. In Q1 2024, there are so far 127 transactions per day – an increase of more than 200% since the launch. The average volume per day is 48.0 billion SEK, which is an increase of approximately 30% since the launch. However, this corresponds to on average 378 million SEK per transaction, which is a decrease of 57% since the launch. To summarise, the volume performance since launch is:

• Transactions per day: 42 to 127 (appr. +202%)
• Volume per day: 36.6 BSEK to 48.0 BSEK (appr. +31%)
• Volume per transaction: 870 MSEK to 378 MSEK (appr. -57%)

So, to summarise, many more but smaller transactions happen each day. The overall daily volume has substantially increased (+11.4 BSEK) as well.

Going back to the chart with the (average) rate at the end of each quarter, we see a massive rate increase in Q3 2022, which is approximately when central banks started to raise interest rates. The SWESTR went from 0.1 to 1.6% in just one quarter. The next quarter it increased by 0.8%, which is also a large increase. In the recent quarters, however, the rate is becoming more stable. In Q4 2023, it just increased by 0.2%. We can also note that the latest SWESTR (3.898%) is actually below or the same as the average of 3.9% at the end of 2023. So, if this continues for the rest of the quarter, we will see a “peak” of the rate in Q1 2024.

In this post, we have effectively used fundamental data analytics principles, to look at the SWESTR performance from many angles – latest rate (“price”), monetary volume and number of transactions. For example the monetary volume is important both for large and small financial actors and investors. If the volume is too large for an actor, the actor might not be confident that the SWESTR is an appropriate rate. On the other hand, if there is too little volume, large actors/investors might not want to use it for their transactions as it gives too little insight to their pricing case. Their transactions could also make up a large portion of the SWESTR volume which is not a desirable property of a reference rate. Finally, we have also reviewed several averages from 2021 to 2024, and the overall volume development since launch, to get some historical background as well.

A closer look at the NOK exchange rate development using Power BI

In this blog post, we will elaborate on the Norwegian krone (NOK) exchange rate development by using Power BI. In our Portfolio, we have built a report which presents NOK exchange rates and bond yields. The data is read from Norges Bank directly to Power BI using their REST API. REST API stands for REpresentational State Transfer API, and it is a common way of structuring and accessing data in a programmatic way.

Introduction to the Power BI report

Our report, called Norwegian krone (NOK) exchange rate & bond yields, presents the NOK performance against an array of currencies. For example, the NOK exchange rate against Canadian Dollar (CAD), Euro (EUR) and US Dollar (USD) are presented. In addition, the NOK bond yields for the government bonds are presented. Finally, the so called “bond spreads” are presented. Bond spread refers to the difference in yield between a short-term bond and a long-term bond. More information about bond spreads is found below.

To start with, the user can compare the percentual and actual development of exchange rates. It is possible to select which currency to compare with, and the time interval can be selected. In our report, we have decided to read data starting from Jauary 2010, but Norges Bank provides data even earlier than that. Monthly and quarterly changes are presented in the matrix for each currency which can be seen below.

NOK performance highlights

The total development since 2010 is presented and some highlights are:

• NOK has strengthened vs BRL (Brazilian real) by 36%
• NOK has weakened vs GBP by 44%
• NOK has weakened vs USD by 82%

To further zoom in on the performance, in the picture below, we have filtered the page on the year 2023 and only selected EUR, GBP and USD for analysis.

To give the user some background on the exchange rates development, we also present bond yields and bond spreads. For example, increases in the NOK government bond yields might correlate with a stronger NOK and vice versa.

Bond spreads

The bond spread is an important measure, as it typically tells how much of a premium you can get by lending your money for a longer time span. For example, if the 3 years bond yield is 3.0% and the 10 years yield is also 3.0%, investors might think there is no reason to “lock” the money for 10 years and could rather go for the shorter time span. On the other hand, a large bond spread, which could happen if the 3 years yield is at 3.0% and the 10 years yield is at 5.0%, could indicate that there are large risks with the 10 years yield, which could scare investors.

Summary page in Power BI

We present a summary page with NOK exchange rates and an extensive summary of NOK bond yields. The durations of the bonds span from 3 months all the way to 10 years. Finally, we present bond spreads in terms of differences between the 10 years yield and the 3/5 years yields. The development is evaluated in many different time periods, for example quarter-to-date (QTD), vs 5 years ago and vs 10 years ago. Hence, it gives a compressed historical view as well as an emphasis on the current values. It is an efficient Power BI visualisation which provides an overview of the most important measures with regards to exchange rates and bond yields development.

For more information on how Norges Bank shares open data, for example related to exchange rates, securities and interest rates, please see their website.

How to read Norwegian krone (NOK) exchange rate data to Power BI

In this blog post, we are going to explain how to read data from Norges Bank to Power BI. We have done so in one of our reports, available in our Portfolio. The report reads data from Norges Bank directly to Power BI. We do so by utilising their REST API, which provides open data. REST API stands for REpresentational State Transfer API, and it is a common way of structuring and accessing data in a programmatic way.

Sometimes, there can be security measures applied to the REST API. In the case of Norges Bank’s API, there are no security measures so the API is fully open. If you are an API developer and wants to earn money by providing an API, you typically make the users pay for access to the API, and security measures make it impossible to reach for non-paying users.

The user starts by going to Get Data in Power BI, which is going to look like this. Then the user selects “Web” in the list presented.

From Norges Bank’s website, we select that we want to read the USD exchange rate from 1st January 2023 until 24th January 2024. We also select that we want to read by API, and we then get the following message.

The URL listed is:

https://data.norges-bank.no/api/data/EXR/B.USD.NOK.SP?format=csv&startPeriod=2023-01-01&endPeriod=2024-01-24&locale=en

Here, EXR means Exchange rate and USD.NOK means that we want to read the USD-NOK exchange rate. We have selected a csv format and the start period and end period are visible in the URL. We simply add this URL to the URL field in Power BI which will look like below.

The user now gets the data from January 2023 in return. Each row of the data is the data for that day, in this case it is the exchange rate at that day.

Depending on the use case, it might be better to formulate requests that do not read all the data at once. For example, the report can have a button to refresh data depending on user selection. Then the user might select 2015 – 2024, and that request can then be sent to the API. This can be a nice solution if there are many potential API requests. Instead of reading all of these, they can be created dynamically. For example, Norges Bank has a section in their data warehouse where they provide Norwegian government securities – Prices and yields. Here, there are many Securities which can be seen below.

They can also be read with different Frequencies and Measurement units. It creates many combinations, so it can be a good idea to dynamically create such request depending on the user selection, rather than to pre-read all of them. In this report, however, it is straightforward and all the currency data is read from 2010.

Finally, the data will look like this in Power BI and now it is time to visualise the data in a meaningful way.

For more information on how Norges Bank shares open data, for example related to exchange rates, securities and interest rates, please see their website.