Exctract specific values from webpages

[Mangorni]

Hi again guys,

I’ve been struggling for a couple of days with this vba/html problem.

The macro is supposed to enter a site which requires username and password, enter the info and redirect to a URL containing “my portfolio”.

At this pint everything goes smoothly, but now I want to extract specific stock prices based on the HTML-code.

As you can see from the uploaded picture I have 5 different stocks in the portfolio, each stock with different id; “tr1”-“tr5”. Normally when I’m extracting info from webpages the “class” is unique but here the class is “kursPlus” for each stock. I have highlighted stock number 1 in the picture and the stock price is 3,01 (in the grey area).

nordnet.PNG

So, my question is, how can I extract each stock price and print them in different cells in excel?

This is my code so far;

Please Login or Register  to view this content.

[Norie]

Can't you use the td elements of the tr element?

Or even use the table's Rows and Cells properties?

[Mangorni]

Thanks for you imidiate response, Norie.

I'm kind of new to coding (especially HTML) so your answer might very well be the solution, but I cannot convert your answer into VBA-code.

I've tried to use getElementsByClassId, but can't get it to work.

Do you mind giving an example code?

Thomas

[Norie]

To get the tr1, tr2 etc table row elements try using getElementById.

Once you have a row element you can loop through the cells in the row to get all the values, or you can get a specific cell value using it's (zero-based) index.

So something like this perhaps, untested though.

Please Login or Register  to view this content.

[Mangorni]

Yeah, I've tried to use getElementbyID, but the same error message returns (Run-time error '438': Object doesn't support this property or method).

Thomas

[Norie]

What exactly did you try?

[Mangorni]

Well I, just replaced my "dd" with yours.

Please Login or Register  to view this content.

Thomas

[AB33]

Range("A1") = IE.document.getElementsByTagName("td")(5).innerText
Range("B1") = IE.document.getElementsByTagName("td")(6).innerText
Range("C1") = IE.document.getElementsByTagName("td")(8).innerText

[Norie]

I might have made that a bit too complicated.

What happens if you try this.

Please Login or Register  to view this content.

[Mangorni]

Thank you, Norie! That really did the trick.

Here is my final code, is there a way to make it shorter/better?

Please Login or Register  to view this content.

[Norie]

Glad that worked.

Strange the code in post #4 didn't work as it's basically the same - perhaps trying to do too much in one go.

[Mangorni]

Actually it's working fine with post #4 as well.

It looks like the crucial point is the time delay.

Please Login or Register  to view this content.

If I remove any of those I just get "Run-time error '424': Object required". You know why?

Learning by doing! Anyway, I really appriciate your help. Thank you.

[Norie]

Timing is always a problem when trying to get data from webpages, that's why you'll often see code like this,

Please Login or Register  to view this content.

which is used to 'pause' the code until the page is ready.

Actually instead of using Application.Wait with a fixed interval you might want to try using the above loop.

[Mangorni]

Cool, that worked even better. Thanks again!

One last thing;

The numbers I exctract from the webpage is stored in excel as text (I guess this comes from .innerText). Is there a way to store the numbers as number (I've tried .values instead of .innertext)?

Or do i need to format the numbers using f.eks 1 * multiply after I've extracted them?

Thomas

[Norie]

Thomas

You could use one of VBA's conversion functions eg Val, CLng, CDbl etc.

Multiplying by 1 should also work.

[Mangorni]

Thank you again. I've finally managed to get the right format in each column.

However, there is one more value I need to extract from the webpage. The value is marked with the grey line (0,29%).

nordnet.PNG

I've tried the following code (among with ALOT of other variants), but i can't get it to work. I guess I need to read up on HTML, but that has to be another time.

Please Login or Register  to view this content.

Can you give me yet another valuable tip on how to extract this value from the webpage?

Thanks in advance.

Thomas

[Mangorni]

I'll try to rip up in this post, one more time. I've tried everything, but can't extract the value marked with the grey line (0,29%).

I also have another question related to the same code (mentioned ni page 1). I really want to make the code as dynamic as possible, so when I buy (or sell) a new stock the code will notice this change.

To do this I think i have to make a loop which goes through all the "tr-elements". However, I've tried but cannot get it to work. Is there anyone in here that has this knowledge of both VBA and HTML?

Thanks in advance.