Monthly Archives: June 2026

Access, Excel

Maven Lego sets

This blog is based on Maven Analytics’ data set LEGO sets, Free Sample Dataset Download – LEGO Sets – Maven Analytics | Build Data Skills, Faster thanks for Maven Analytics for publishing this data set. 

The questions published are here:

  1. How many LEGO sets have been released since 1970? Is there a noticable trend?

2. Is there a relationship between the price of a set and its number of pieces?

3. Which has been the most popular theme in each decade?

4. Are LEGO minifigures most closely tied to licensed sets?

I will answer the questions based on the sample data at the end of this blog post.

First we need to download the data.

I took data – text to columns.

Data looks like this in Excel.

I downloaded the data into Access.

Set_ID was marked as indexed and I changed manually set_id into short text data type and not to be indexed. I changed decimal symbol to dot.

The data is in the Access and I will make a Power Pivot in Excel based on the Access table. As stated earlier, once data is in Access, I can query data with SQL.

  1. How many LEGO sets have been released since 1970? Is there a noticeable trend?

Totally 18457 Lego sets have been released.

I checked with SQL that values are unique.

SELECT DISTINCT

    Count(set_id)

FROM

    LS2;

Results in Access.

This is the same result from Excel power pivot.

How many Legosets have been released annually, that we can find out with this SQL query:

SELECT

    YEAR,

    COUNT(YEAR)

FROM

    Lego_sets

GROUP BY

    year;

Results are not very readable, as the list so long.

We can take the report via Power Pivot and create a graph of the results.

We can see a clear increasing trend is visible, especially starting from year 1990.  

2. Is there a relationship between the price of a set and its number of pieces?

Not all the fields in have values in pieces are retail value fields. I have taken only the records that both the fields have values, 5321 records altogether.

This correlation is counted from Excel.

A value close to 0,9 is very clear positive correlation. The more pieces in a Lego set, the more expensive the Lego set is.

3. Which has been the most popular theme in each decade?

I added a new column in the Excel to define the decades. Years 1970-1979 belong to 1970s.

The sentence is simply: =ROUNDDOWN(C2;-1). That is rounding down to full ten.

This is a pivot table by theme group and decade.

To find it easily which is the highest value per decade, we could use conditional formatting.

More rules.

Format only top or bottom ranked values, then select top 1. Only the highest value will be formatted. Press format.

The highest value will be formatted with yellow filling.

When you have created the rule, you can just select manage rules.

2000s, 2010s and 2020s the miscellaneous is the most popular theme group. Normally, miscellaneous should be an additional group not a major group. Could the grouping be made differently ?

1970s: vintage

1980s: modern day

1990s: pre-school

2000s: miscellaneous

2010s: miscellaneous

2020s: miscellaneous

4. Are LEGO minifigures most closely tied to licensed sets?

Licensed is a theme group.

A pivot table was created theme group in rows and minifigs in values.

The Licensed theme group is the mostly tied to mini figures.

We have the same results with Access.

This is the SQL query in Access.

SELECT

    themeGroup,

    COUNT(minifigs)

FROM

    LS2

GROUP BY

    themeGroup

ORDER BY

    COUNT(minifigs) DESC;

Here are my answers in short:

  1. How many LEGO sets have been released since 1970? Is there a noticable trend?

18457, there is a clear increasing trend.

2. Is there a relationship between the price of a set and its number of pieces?

Yes, there is a clear positive correlation.

3. Which has been the most popular theme in each decade?

1970s: vintage

1980s: modern day

1990s: pre-school

2000s: miscellaneous

2010s: miscellaneous

2020s: miscellaneous

4. Are LEGO minifigures most closely tied to licensed sets?

Yes.

Uncategorized

Custom PMT

The standard payment function PMT calculates the payment of the loan.

We need to enter

  1. interest rate of the loan
  2. number of payment periods, that means what is the payback period
  3. present value or the loan capital, how much loan are we taking.

These are the mandatory arguments for the PMT function.

Interest rate is per month, so we need to divide the rate by 12. Number of payment period is 22 multiplied by 12 as payback period is 22 years. The loan is 200 000. The payment function returns the value 1087.

We needed to have a formula for rate and number of payments. The function return the value in negative value. Also 1087,36 might not be exactly same as what bank tells us if we ask the bank how much is the monthly payment for a loan of 200 000, payback time of 22 years, and with 3,5 % interest. Bank might include eg. a transaction fee of 1,1 € for each payment.

We can include the transaction fee in the PMT sentence. A bank might add a transaction fee for each annuity. Even though the transaction cost does not sound very high, it is good to include in the calculations.

If we are using often PMT function or we make an important decision and we want to calculate the exact value, we might consider creating own payment function.

Function z_pmt(z_rate, z_nper, z_pv)

    z_pmt = ((z_rate / 12) * z_pv) / (1 – (1 + z_rate / 12) ^ (-z_nper * 12))

End Function

Here we have a customized payment function. It takes into consideration that rate is divided by 12 and number of periods is multiplied by 12. The function return the value in positive. Our function holds three arguments: rate (z_rate), number of periods (z_per), and loan capital (z_pv). Those are in the same order as with PMT.

I am not going into details how the formula was built. Financial mathematic books shed light how annuity is calculated manually.

The function could automatically round into two decimals and add fixed transaction fee 1,1 € per payment.

Function z_pmt(z_rate, z_nper, z_pv)

    z_pmt = Round(((z_rate / 12) * z_pv) / (1 – (1 + z_rate / 12) ^ (-z_nper * 12)), 2) + 1.1

End Function

As PMT returns a negative value, we need to minus 1,1 in order to add the value. Customized Z_PMT counts positive values, so we add 1,1. In VBA decimal separator is dot independently what we have defined in application. Comma is separating arguments in VBA.

Maybe bank presents 0,5 % higher monthly payment than PMT function, for some reason. If you want to simulate monthly payments from the bank, we can simply multiply PMT function with 1,005 or just modify custom Z_PMT.

Function z_pmt(z_rate, z_nper, z_pv)

    z_pmt = Round(((z_rate / 12) * z_pv) / (1 – (1 + z_rate / 12) ^ (-z_nper * 12)) * 1.005, 2)

End Function

PMT is one of the best functions in Excel. If you ever consider taking loan from the bank, check calculations from bank with PMT in Excel.

If you need to modify the function, it can be useful to have your own customized function. Also taking a house loan might be the biggest finance decision you ever make. Therefore it is better to be precise when you calculate monthly payment for your house loan. That you don’t forget to budget a transaction fee of 1,1 € which bank might charge you per every transaction.