Excel Web scraping loop and filling the relevant cells

[lesoies]

Hello,

Basically i m trying to scrap stocks data in my excel for my portfolio of stocks but so far I havent been able to achieve it , I want to import data which is in the response... the code i used is:

Please Login or Register  to view this content.

How can I just get the required data and fill into the relevant fields? The data i need Date , Opening rate , High Rate, Low Rate, Closing Rate , Total filled in the a row ... for example

2nd oct, 2012 | Opening rate here | High Rate Here | Low Rate Here | Closing Rate Here | Total Here

please help me out , after this i will need to loop this for all the range i want. waiting for reply eagarly

Regards,
Aimsh

[lesoies]

The data returned from the website is below, i only need to extract the data in BOLD and UNDERLINED in cells

Please Login or Register  to view this content.

[Kyle123]

Maybe:

Please Login or Register  to view this content.

[lesoies]

Thank you Kyle!! This code works perfectly.... Okay if I need to run a loop now this should be easy but for some days the data returns no records found , so I can probably add a IF to setup to check if the record is there or not!.
Now i need to program a UserForm to define range of date for which i need the data to be inserted in the fields.

[Kyle123]

Glad it helped

[lesoies]

Is there a way to fill the next unfilled rows with data ? i mean i just added the above data in ROW 1 , how can i setup it to fill the next row ? i mean a way to check how much rows are there and fill the next blank row

[Kyle123]

Yes, but you'd need to know how you'd populate the variables in the GetValues function within the loop. If yuo can post the code for how you think you'd do that, I'll have a look and adapt

[lesoies]

Kyle, can you modify the above code with a loop and a date range ? like i give the date range to fetch , for example 2 September 2012 - 3 Oct 2012 and then it fills the data in starting A_ "_" being the first unfilled row ?

[lesoies]

Okay let me try that myself first

[Kyle123]

Will it need to pull data from weekends or just week days?

[lesoies]

Only need to pull data from Week days..

[lesoies]

Okay what i think i should be doing is change the getvalue function you sent to something like :

Please Login or Register  to view this content.

I still cant figure out the rest of the code ... but it should be like split tdate into day , month , yr .. then get that data from the website , then do +1 in tdate and so on and on .. along with a filter to check if there was any record found or not , if not then skip it .. how does it sound ? can you do this for me as its taking me a lot of researching, but still i m learning from it

Thanks,

Aimsh

[Kyle123]

Does this help?

Please Login or Register  to view this content.

[lesoies]

how will i run this code ?

[Kyle123]

I'm intrigued as to how you formulated the code in your first post if you don't know how to call a sub. The code in your first post is somewhat more advanced than the code I posted for looping.

You'd call it in exactly the same way you'd call any sub or function with parameters in VBA.

Something like

Please Login or Register  to view this content.

[lesoies]

Well i changed the code you sent me to a function ... like this

Please Login or Register  to view this content.

And its works fine , just working on adding the filter to skip if the results no record and then i can proceed working on other task that i need to do !

Thanks for your help again Kyle

[Kyle123]

Well it's not a function, it's a sub - functions should return something. If you change it back to a sub you'll see that it runs in exactly the same way.

The filter is very simple to work out, don't overthink it

Good luck

[lesoies]

Well I m not good with VB at all , my first time doing it , I have experience with PHP, thats why having trouble understanding this for the filter i have devised this:

Please Login or Register  to view this content.

I should call getvalues once though ... will improve it ..

[lesoies]

by the way, i was thinking is there any console for Excel VBA from where i can test things out more easily and which refers excel specific functions as well ?

[Kyle123]

Have a look at the immediate window, you can type commands and functions into it directly, subs are typed in, functions proceeded with ?

This would be a better check, you're calling the function twice so it's slowing your code right down:

Please Login or Register  to view this content.

[lesoies]

I m getting a type mismatch error any idea why ?

[Kyle123]

What have you declared values as? - should be a variant

[lesoies]

Same error ....

Please Login or Register  to view this content.

[Kyle123]

No, not a variant array, a variant:

Please Login or Register  to view this content.

[lesoies]

still not working dude

[lesoies]

Here's the complete code below, any ideas what is the problem !

Please Login or Register  to view this content.

[Kyle123]

Yes, you can't test the upper bound of a boolean, it's not an array. Replace:

Please Login or Register  to view this content.

With:

Please Login or Register  to view this content.

If the code finds no records for that date, it will, by default return an array with 1 element, when records are found 6 elements are returned.

My proposed check looks at the bounds of the returned array to see how many elements are returned

[lesoies]

Thanks alot its working finally now I guess i will be needing your help in the future as well hehe

[lesoies]

Hey, Kyle I tried using the code again and its not working it keeps giving some debug error ... can you please check it ? the HTML is returning Method not implemented

[Kyle123]

That web page no longer exists, so you'll need to find out where to get the info you're after and you'll need a new macro

[lesoies]

The website still exists but its returning error only on Excel, I dunno whats wrong but if you visit www.kse.com.pk and then go to Market Information and then Historical Data and then select By Symbol you will see it still exists!! and I did check with Firebug the POST and other details are also same! so any idea why this error is occuring ?

[lesoies]

Hey Kyle, I fixed it myself I added MS2XML.ServerXMLHTTP.6.0 and it started working again, can you help me make the monthy adder? the one i m using is the daily records