Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Web site data to excel columns


28 Jun 2013   #1

Windows 7 Ultimate x64
 
 
Web site data to excel columns

Is it possible to fetch some lines of data from a group of web pages and paste in to Excel?Pls view the image for required excel columns.

urls are,
Stock Share Price dlf ltd | Get Quote dlf | BSE
Stock Share Price dlf ltd | Get Quote dlf | BSE
Stock Share Price jaiprakash associates ltd | Get Quote jaipra | BSE
------------
-----------
up to 100 urls in a text file.



Attached Thumbnails
Web site data to excel columns-capture.jpg  
My System SpecsSystem Spec
.

28 Jun 2013   #2

W7 Pro SP1 64bit
 
 

When you right click on the webpage and select "View source" from the context menu - do you see the info of interest; I don't. If the info were there, you could use InetGet. They know the value of the info being presented and they probably want to discourage automated data mining.

See if there is a mobile version of the webpage.
My System SpecsSystem Spec
29 Jun 2013   #3

Windows 7 Ultimate x64
 
 

the mobile version of the site is BSE - Get Quote
only changing the last six numbers give data of different symbols.This site is more useful than the desktop version.
Ill explain my idea for autoit script.
1.a note pad contain the symbol codes(500087,500047,500030....)in different lines
2.the script use a default url- BSE - Get Quote
3.fill the space after "=" with the six no. from the note pad.thus the script can make up to 100 urls.
4.copy the required data and paste to excel columns-I dont know how to do that.
i make some change in excel columns -the "id" from html source
1-spanCname
2-strongDate
3-spanchangVal
4-strongCvalue
5,6-tdDHL ( in the site two values seperated by"/"-in excel i want two values in two columns)
7-tdWAPName
8-tdTTQW
pls reffer attached new image .


Attached Thumbnails
Web site data to excel columns-new-img.jpg  
My System SpecsSystem Spec
.


29 Jun 2013   #4

W7 Pro SP1 64bit
 
 

edit: I see that you posted while I was creating my post below:

There is a mobile version of the website :-)


The basic flow of your script would be:

open/read the text file full of URLs into a variable
split that variable into an array
loop thru the array

for each URL, get the numbers on the end
http://www.bseindia.com/stock-share-price/dlf-ltd/dlf/532868/

use _INetGetSource to download the info into a variable
http://m.bseindia.com/StockReach.aspx?scripcd=532868
split that variable into an array
loop thru the array

use the string functions to find the info of interest
write the info to a comma delimited file

You can work on code to do the steps mentioned above. Use the other scripts that you have as a guide. Let me know what parts you cannot figure out.
My System SpecsSystem Spec
29 Jun 2013   #5

W7 Pro SP1 64bit
 
 

Quote   Quote: Originally Posted by thanku View Post
~~~
4.copy the required data and paste to excel columns-I dont know how to do that.
~~~
You will not need to copy/paste anything. AutoIt can load the webpage into RAM. Then you can read thru each line of the webpage electronically while it is in RAM. There will be not browser window showing for this.

When you come upon a line that has some info of interest, you can write that info to a text file, just put a comma after the data and that comma will start a new column within Excel when you open the text file.
My System SpecsSystem Spec
29 Jun 2013   #6

Windows 7 Ultimate x64
 
 

Code:
ConsoleWrite(_INetGetSource('http://www.autoitscript.com'))
then where i can find the data for next step? I spend a lot of time googling and try help cant solve my doubts.

I made a notepad manually with all the numbers line by line.I think thats the only thing i can do.
My System SpecsSystem Spec
29 Jun 2013   #7

W7 Pro SP1 64bit
 
 

Quote   Quote: Originally Posted by UsernameIssues View Post
~~~
The basic flow of your script would be:

open/read the text file full of URLs into a variable
split that variable into an array
loop thru the array

for each URL, get the numbers on the end
http://www.bseindia.com/stock-share-price/dlf-ltd/dlf/532868/

use _INetGetSource to download the info into a variable
BSE - Get Quote532868
split that variable into an array
loop thru the array

use the string functions to find the info of interest
write the info to a comma delimited file

You can work on code to do the steps mentioned above. Use the other scripts that you have as a guide. Let me know what parts you cannot figure out.
Try and follow the outline quoted above.

The first step is to open/read the text file full of URLs into a variable

Look back at this post:
BAT or VB file help
Quote:
$whole_file = FileRead($file)
The contents of the file we are currently working with is read and put into the variable named $whole_file.
So, you can use a line like:
$whole_file = FileRead("path to file with original URLs")
where path to file with original URLs is a real path to the original file that contains the URLs

Also in that tutorial is this:
$sentence_array = StringSplit($whole_file, @CRLF, 1)
So, you can use a line like:
$URL_array = StringSplit($whole_file, @CRLF, 1)


Using a combination of the AutoIt help file and the tutorial that explained a bit about the other script, you should be able to put together some code. Just take it one step at a time. Try and follow the outline quoted above. Post the code that you are having trouble with and I'll see if I can help you out.
My System SpecsSystem Spec
29 Jun 2013   #8

W7 Pro SP1 64bit
 
 

Quote   Quote: Originally Posted by thanku View Post
~~~~
I made a notepad manually with all the numbers line by line.I think thats the only thing i can do.
Sorry, but you don't need that and you don't want to be doing stuff manually.

It will take us some time to work thru the outline that I posted :-(

It would be quicker if I just wrote the script for you... :-)
...but you would not learn as much if I did that :-(
My System SpecsSystem Spec
29 Jun 2013   #9

Windows 7 Ultimate x64
 
 

Thank you very much for the advice,
##for each URL, get the numbers on the end
Stock Share Price dlf ltd | Get Quote dlf | BSE ##
we can ignore this line,My text file contain only numbers.Pls check attached file.

so we need to call each line and attach with http : // m.bseindia.com / StockReach.aspx?scripcd=
what function i use for it?


Attached Files
File Type: txt symbol.TXT (152 Bytes, 3 views)
My System SpecsSystem Spec
29 Jun 2013   #10

W7 Pro SP1 64bit
 
 

Sure - if you want to start with that text file with the numbers - that is good.


Put this near the top of the script...
#include<inet.au3>

...then you can use this inside your loop:
$page_source = _INetGetSource("http://m.bseindia.com/StockReach.aspx?scripcd=532532", 1)

I'll show you how to add the red part after I see your code that:
...reads the file full of numbers
...splits those lines into an array
...and loops thru each line.
My System SpecsSystem Spec
Reply

 Web site data to excel columns




Thread Tools




Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 09:00 PM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33