# Power Query (web page to Excel) - when there is no table?



## GabrielAIK (Apr 24, 2018)

Hi,

I would like to fetch data from an Internet page to Excel, but the problem is that the web page in question does not have a table that is importable. 

Is it still possible to fetch the data, or is there no hope?

I'd like to fetch information from a website called Avanza.

E.g. this stock: https://www.avanza.se/aktier/om-aktien.html/82987/nokia-oyj

If you scroll down you see something called "Mäklarstatistik" (Broker statistics). I want to sort this by "Köpt" and then fetch the top 5 rows to Excel.

Is this possible?

Thanks!


----------



## Norie (Apr 24, 2018)

That data is in a table.

What's happening when you try to access it using Power Query?


----------



## GabrielAIK (Apr 24, 2018)

Oh alright!

I thought there needed to be a yellow arrow next to the table, for me to be able to select it. However, I cannot see such an arrow.

I can access the website using Power Query, but it looks just as if I opened it through a regular Chrome session.


----------



## GabrielAIK (Apr 24, 2018)

I think I fixed it?

I imported the whole website to a sheet, and then just removed all rows that I did not want. Then I went to the "Köpt"-column, and in Excel sorted from Largest to Smallest.

Should be it? I will double check later on when there are updated trades


----------



## Norie (Apr 24, 2018)

When I use goto Data>New Query>From Other Sources>From Web and enter the URL you posted I, eventually get to a Navigator page.

The Navigator page has 2 tabs, Table View and and Web View, and when I select Table View I get a list of tables.

The table you want, I think, is Table 0.


----------



## GabrielAIK (Apr 24, 2018)

Norie said:


> When I use goto Data>New Query>From Other Sources>From Web and enter the URL you posted I, eventually get to a Navigator page.
> 
> The Navigator page has 2 tabs, Table View and and Web View, and when I select Table View I get a list of tables.
> 
> The table you want, I think, is Table 0.



Perhaps I have another version. I cannot find a Navigator page in my "New Web Query". Where do you see it?


----------



## Norie (Apr 24, 2018)

These are the steps I followed.

1 Opened a new instance of Excel.

2 Went to the Data>Get & Transform tab, this might be different on different versions of Excel.

3 Selected New Query>From Other Sources>From Web.

4 In the From Web dialog I entered the URL you posted and clicked OK.

5 This opened the Navigator dialog I mentioned, where I selected Table 0 and clicked Load.


----------



## GabrielAIK (Apr 24, 2018)

Norie said:


> These are the steps I followed.
> 
> 1 Opened a new instance of Excel.
> 
> ...



Alright, thanks!

Seems we have different versions.

My steps:

1. Open Excel
2. Press on the Data tab
3. Press "From Web"
4. In the Adress-field, I enter the link: https://www.avanza.se/aktier/om-aktien.html/82987/nokia-oyj
5. Press Go

Then it looks like this: https://i.imgur.com/NeHxJSa.jpg


----------



## Norie (Apr 24, 2018)

That doesn't look like Power Query.

Which version of Excel do you have?


----------



## GabrielAIK (Apr 24, 2018)

Norie said:


> That doesn't look like Power Query.
> 
> Which version of Excel do you have?



I am using Microsoft Office Professional Plus 2016


----------



## GabrielAIK (Apr 24, 2018)

Hi,

I would like to fetch data from an Internet page to Excel, but the problem is that the web page in question does not have a table that is importable. 

Is it still possible to fetch the data, or is there no hope?

I'd like to fetch information from a website called Avanza.

E.g. this stock: https://www.avanza.se/aktier/om-aktien.html/82987/nokia-oyj

If you scroll down you see something called "Mäklarstatistik" (Broker statistics). I want to sort this by "Köpt" and then fetch the top 5 rows to Excel.

Is this possible?

Thanks!


----------



## Norie (Apr 24, 2018)

Do you have a Get & Transform section on the Data tab?

Or a New Query button?


----------



## GabrielAIK (Apr 24, 2018)

Norie said:


> Do you have a Get & Transform section on the Data tab?
> 
> Or a New Query button?



Oh! 

I had just went from Data straight to pressing "From Web"

But there is also an alternative route, going from Data -> New Query -> From other sources -> Web

There, I enter the URL, select the "Basic" version then press "OK".

Then it connects to the site, and thereafter there is a Navigator window that pops up.

To the left, under "Display Options", I can see all the tables.

As you said, Table 0 is the one I was looking for. So I simply pressed on it, and then on the "Load" button, and it was sent to Excel.

Now, I'm wondering, is it possible to automatically sort it from largest to smallest under column C ("Köpt")?

Or do I need to update Excel sheet using alt+A+R+A

And then manually select the numbers shown in Column C and sort from Largest to smallest?


----------



## Norie (Apr 24, 2018)

Not sure what you mean, in the Power Query editor you can sort the Köpt using the arrow next to the header and you can do the same when you return the table to Excel.


----------



## GabrielAIK (Apr 24, 2018)

Norie said:


> Not sure what you mean, in the Power Query editor you can sort the Köpt using the arrow next to the header and you can do the same when you return the table to Excel.



I see, I opened the Query editor, and under "Köpt" I wanted to sort it by Descending to get the highest values at the top.

However, the website uses a spacing which the Query Editor doesn't recognize. Is is possible to fix this in some way?

Like transforming all " " to "", enabling it to sort the numbers?


----------



## Norie (Apr 24, 2018)

So what should a value like 9 521 be?

Is it 9,521?

Also, in the other columns should 5071 actually be 50.71 (50,71)?


----------



## GabrielAIK (Apr 24, 2018)

Norie said:


> So what should a value like 9 521 be?
> 
> Is it 9,521?
> 
> Also, in the other columns should 5071 actually be 50.71 (50,71)?



Yes exactly!

I received some help yesterday on the forum regarding this matter, using substitution formulas, e.g.: =IFERROR(SUBSTITUTE(SUBSTITUTE(W48,",",".")," ","")+0,"-")


----------



## Norie (Apr 24, 2018)

In Power query select all the columns except Mäklare.

Right click one of the columns and select Change Type>Using Locale.

In the Change Type with Locale select a suitable data type, e.g. Currency, Decimal Number, from the first drop down.

In the Locale drop down select the relevant locale, which I think is Swedish (Sweden).

Click OK.

You should now have a table with sortable columns.


----------



## GabrielAIK (Apr 24, 2018)

Norie said:


> In Power query select all the columns except Mäklare.
> 
> Right click one of the columns and select Change Type>Using Locale.
> 
> ...



Thank you very much!!

I chose Decimal numbers for the share prices, and then "Whole numbers" for the volumes which seems to solve the problem.

I'll give it a try tonight when I need to update my Excel sheet.

Thank you very much for your patience and help, appreciate it a lot!


----------

