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

29 Jun 2013   #11
thanku

Windows 7 Ultimate x64
 
 

Code:
#include<inet.au3>
$file = symbol.txt
        $whole_file = FileRead($file)
    $file_handle = FileOpen($file, 2)
	$word_array = StringSplit($whole_file, @CRLF, 1)
	For $i = 1 To $word_array[0]
        If $word_array[$i] = "" Then ContinueLoop
		   For $ii = 1 To $word_array[0]
            If $ii = 1 Then $word_array[$ii] &= "http://m.bseindia.com/StockReach.aspx?scripcd="i)
            If $ii = $word_array[0] Then
			   Exit
			Else
			Else
			Else
			   $page_source = _INetGetSource(i, 1)
    FileWrite($file_handle)
    FileClose($file_handle)
Thanks for the encourage,This is the maximum i can make,Pls correct and finish it when you got enough free time


My System SpecsSystem Spec
.
30 Jun 2013   #12
UsernameIssues

W7 Pro SP1 64bit
 
 

My apologies.

I misjudged your level of understanding of the past code that we have used.

I should have had you work on this in much smaller tasks.

Try this script:
Code:
Opt("TrayIconDebug", 1)

$S_running = "~stock-stuff~" ;name the script
If WinExists($S_running) Then Exit
AutoItWinSetTitle($S_running)

#include<Inet.au3>
#include <String.au3>

$whole_file = FileRead("symbol.txt")
$account_number_array = StringSplit($whole_file, @CRLF, 1)

$file_h = FileOpen("output.csv", 2)

For $i = 1 To $account_number_array[0]
    If $account_number_array[$i] = "" Then ContinueLoop ;skip empty lines

    $page_source = _INetGetSource("http://m.bseindia.com/StockReach.aspx?scripcd=" & $account_number_array[$i], 1)

    $spanCname = _StringBetween($page_source, '<span id="spanCname" class="companyname">', '</span><br>')
    $strongDate = _StringBetween($page_source, '<span id="strongDate">', '</span>')
    $spanchangVal = _StringBetween($page_source, '<span id="spanchangVal" class="srcovalue">', '</span>')
    $strongCvalue = _StringBetween($page_source, '<span class="srcovalue"><strong id="strongCvalue">', '</strong></span>')
    $tdDHL = _StringBetween($page_source, '<td id="tdDHL" class="TTRow_right">', '</td>')
    If StringInStr($tdDHL[0], "/") Then
        $tdDHL_array = StringSplit($tdDHL[0], "/", 1)
    Else
        Dim $tdDHL_array[3]
        $tdDHL_array[1] = "null"
        $tdDHL_array[2] = "null"
    EndIf
    $tdWAp = _StringBetween($page_source, '<td id="tdWAp" class="TTRow_right">', '</td>')
    $tdTTQW = _StringBetween($page_source, '<td id="tdTTQW" class="TTRow_right">', '</td>')

    FileWriteLine($file_h, _
            $account_number_array[$i] & "," & _
            $spanCname[0] & "," & _
            $strongDate[0] & "," & _
            $spanchangVal[0] & "," & _
            $strongCvalue[0] & "," & _
            $tdDHL_array[1] & "," & _
            $tdDHL_array[2] & "," & _
            $tdWAp[0] & "," & _
            $tdTTQW[0])
Next
FileClose($file_h)
I temporarily added the stock account number to the output.
My System SpecsSystem Spec
30 Jun 2013   #13
thanku

Windows 7 Ultimate x64
 
 

Thank you very much,I dont think it simple.It will take six months for me to make a script like this.
I just add a simple msgbox at end to show its finished.
in the out put file
Code:
  $spanchangVal = _StringBetween($page_source, '<span id="spanchangVal" class="srcovalue">', '</span>')
this line gives a colum with value 37(5.3%) is it possible to split 37 in one colum and 5.3% in another column.
also i need to add heading to excel columns,

code name date change %change close high low wap ttq

These are not important and necessary!
My System SpecsSystem Spec
.

30 Jun 2013   #14
UsernameIssues

W7 Pro SP1 64bit
 
 

Do you want "LTD- " by the date?
Web site data to excel columns-stk.png

Added:
column headers
split change and %change
error checking to stop if output is locked by Excel
tray tip to so progress of the data pull
Code:
Opt("TrayIconDebug", 1)

$S_running = "~stock-stuff~" ;name the script
If WinExists($S_running) Then Exit
AutoItWinSetTitle($S_running)

#include<Inet.au3>
#include <String.au3>


$whole_file = FileRead("symbol.txt")
$account_number_array = StringSplit($whole_file, @CRLF, 1)

$file_h = FileOpen("output.csv", 2)
If $file_h = -1 Then
    MsgBox(0, "AutoIt", "Could not open output file. Close Excel?")
    Exit
EndIf

FileWriteLine($file_h, _
        "Code" & "," & _
        "Name" & "," & _
        "Date" & "," & _
        "Change" & "," & _
        "% Change" & "," & _
        "Close" & "," & _
        "High" & "," & _
        "Low" & "," & _
        "Wtd. Avg Price" & "," & _
        "TTQ")

For $i = 1 To $account_number_array[0]
    TrayTip("Pulling stock data ", "Stock " & $i & " of " & $account_number_array[0], 100)
    If $account_number_array[$i] = "" Then ContinueLoop ;skip empty lines

    $page_source = _INetGetSource("http://m.bseindia.com/StockReach.aspx?scripcd=" & $account_number_array[$i], 1)

    $spanCname = _StringBetween($page_source, '<span id="spanCname" class="companyname">', '</span><br>')
    If @error Then _errors($account_number_array[$i])

    $strongDate = _StringBetween($page_source, '<span id="strongDate">', '</span>')
    If @error Then _errors($account_number_array[$i])

    $spanchangVal = _StringBetween($page_source, '<span id="spanchangVal" class="srcovalue">', '</span>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($spanchangVal[0], "(") Then
        $spanchangVal_array = StringSplit($spanchangVal[0], "(", 1)
        $spanchangVal_array[1] = StringStripWS($spanchangVal_array[1], 8)
        $spanchangVal_array[2] = StringReplace($spanchangVal_array[2], ")", "")
        $spanchangVal_array[2] = StringStripWS($spanchangVal_array[2], 8)
    Else
        Global $spanchangVal_array[3]
        $spanchangVal_array[1] = "null"
        $spanchangVal_array[2] = "null"
    EndIf

    $strongCvalue = _StringBetween($page_source, '<span class="srcovalue"><strong id="strongCvalue">', '</strong></span>')
    If @error Then _errors($account_number_array[$i])

    $tdDHL = _StringBetween($page_source, '<td id="tdDHL" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($tdDHL[0], "/") Then
        $tdDHL_array = StringSplit($tdDHL[0], "/", 1)
    Else
        Global $tdDHL_array[3]
        $tdDHL_array[1] = "null"
        $tdDHL_array[2] = "null"
    EndIf

    $tdWAp = _StringBetween($page_source, '<td id="tdWAp" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])

    $tdTTQW = _StringBetween($page_source, '<td id="tdTTQW" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])

    FileWriteLine($file_h, _
            $account_number_array[$i] & "," & _
            $spanCname[0] & "," & _
            $strongDate[0] & "," & _
            $spanchangVal_array[1] & "," & _
            $spanchangVal_array[2] & "," & _
            $strongCvalue[0] & "," & _
            $tdDHL_array[1] & "," & _
            $tdDHL_array[2] & "," & _
            $tdWAp[0] & "," & _
            $tdTTQW[0])
Next
FileClose($file_h)
MsgBox(0, "AutoIt", "Finished!")

Func _errors($pass)
    MsgBox(0, "AutoIt", "Error - not a valid stock number: " & $pass)
    ShellExecute("http://m.bseindia.com/StockReach.aspx?scripcd=" & $pass)
    FileClose($file_h)
    Exit
EndFunc

edit:
Same as above - only without "LTD- " by the date:
Code:
Opt("TrayIconDebug", 1)

$S_running = "~stock-stuff~" ;name the script
If WinExists($S_running) Then Exit
AutoItWinSetTitle($S_running)

#include<Inet.au3>
#include <String.au3>


$whole_file = FileRead("symbol.txt")
$account_number_array = StringSplit($whole_file, @CRLF, 1)

$file_h = FileOpen("output.csv", 2)
If $file_h = -1 Then
    MsgBox(0, "AutoIt", "Could not open output file. Close Excel?")
    Exit
EndIf

FileWriteLine($file_h, _
        "Code" & "," & _
        "Name" & "," & _
        "Date" & "," & _
        "Change" & "," & _
        "% Change" & "," & _
        "Close" & "," & _
        "High" & "," & _
        "Low" & "," & _
        "Wtd. Avg Price" & "," & _
        "TTQ")

For $i = 1 To $account_number_array[0]
    TrayTip("Pulling stock data ", "Stock " & $i & " of " & $account_number_array[0], 100)
    If $account_number_array[$i] = "" Then ContinueLoop ;skip empty lines

    $page_source = _INetGetSource("http://m.bseindia.com/StockReach.aspx?scripcd=" & $account_number_array[$i], 1)

    $spanCname = _StringBetween($page_source, '<span id="spanCname" class="companyname">', '</span><br>')
    If @error Then _errors($account_number_array[$i])

    $strongDate = _StringBetween($page_source, '<span id="strongDate">LTD- ', '</span>')
    If @error Then _errors($account_number_array[$i])

    $spanchangVal = _StringBetween($page_source, '<span id="spanchangVal" class="srcovalue">', '</span>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($spanchangVal[0], "(") Then
        $spanchangVal_array = StringSplit($spanchangVal[0], "(", 1)
        $spanchangVal_array[1] = StringStripWS($spanchangVal_array[1], 8)
        $spanchangVal_array[2] = StringReplace($spanchangVal_array[2], ")", "")
        $spanchangVal_array[2] = StringStripWS($spanchangVal_array[2], 8)
    Else
        Global $spanchangVal_array[3]
        $spanchangVal_array[1] = "null"
        $spanchangVal_array[2] = "null"
    EndIf

    $strongCvalue = _StringBetween($page_source, '<span class="srcovalue"><strong id="strongCvalue">', '</strong></span>')
    If @error Then _errors($account_number_array[$i])

    $tdDHL = _StringBetween($page_source, '<td id="tdDHL" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($tdDHL[0], "/") Then
        $tdDHL_array = StringSplit($tdDHL[0], "/", 1)
    Else
        Global $tdDHL_array[3]
        $tdDHL_array[1] = "null"
        $tdDHL_array[2] = "null"
    EndIf

    $tdWAp = _StringBetween($page_source, '<td id="tdWAp" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])

    $tdTTQW = _StringBetween($page_source, '<td id="tdTTQW" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])

    FileWriteLine($file_h, _
            $account_number_array[$i] & "," & _
            $spanCname[0] & "," & _
            $strongDate[0] & "," & _
            $spanchangVal_array[1] & "," & _
            $spanchangVal_array[2] & "," & _
            $strongCvalue[0] & "," & _
            $tdDHL_array[1] & "," & _
            $tdDHL_array[2] & "," & _
            $tdWAp[0] & "," & _
            $tdTTQW[0])
Next
FileClose($file_h)
MsgBox(0, "AutoIt", "Finished!")

Func _errors($pass)
    MsgBox(0, "AutoIt", "Error - not a valid stock number: " & $pass)
    ShellExecute("http://m.bseindia.com/StockReach.aspx?scripcd=" & $pass)
    FileClose($file_h)
    Exit
EndFunc
Web site data to excel columns-stk2.png


edit2:
added some error checking as some webpages caused the script to error out.


My System SpecsSystem Spec
30 Jun 2013   #15
thanku

Windows 7 Ultimate x64
 
 

Thank you very much,the second script without "LTD" is good.Its amazing im trying to add more columns and I dont want to waste your time any more.
My System SpecsSystem Spec
30 Jun 2013   #16
UsernameIssues

W7 Pro SP1 64bit
 
 

I sent you a PM about the last change.

Be sure that the copy that you are working with ends in EndFunc.

It should not be too hard to add columns... you can post your modified version and I'll see I notice any problems.
My System SpecsSystem Spec
30 Jun 2013   #17
thanku

Windows 7 Ultimate x64
 
 

Code:
Opt("TrayIconDebug", 1)

$S_running = "~stock-stuff~" ;name the script
If WinExists($S_running) Then Exit
AutoItWinSetTitle($S_running)

#include<Inet.au3>
#include <String.au3>


$whole_file = FileRead("symbol.txt")
$account_number_array = StringSplit($whole_file, @CRLF, 1)

$file_h = FileOpen("output.csv", 2)
If $file_h = -1 Then
    MsgBox(0, "AutoIt", "Could not open output file. Close Excel?")
    Exit
EndIf

FileWriteLine($file_h, _
        "Code" & "," & _
        "Name" & "," & _
        "Date" & "," & _
        "Change" & "," & _
        "% Change" & "," & _
        "Close" & "," & _
        "High" & "," & _
        "Low" & "," & _
        "Wtd. Avg Price" & "," & _
	    "TTQ" & "," & _
        "2WQ" )

For $i = 1 To $account_number_array[0]
    TrayTip("Pulling stock data ", "Stock " & $i & " of " & $account_number_array[0], 100)
    If $account_number_array[$i] = "" Then ContinueLoop ;skip empty lines

    $page_source = _INetGetSource("http://m.bseindia.com/StockReach.aspx?scripcd=" & $account_number_array[$i], 1)

    $spanCname = _StringBetween($page_source, '<span id="spanCname" class="companyname">', '</span><br>')
    If @error Then _errors($account_number_array[$i])

    $strongDate = _StringBetween($page_source, '<span id="strongDate">LTD- ', '</span>')
    If @error Then _errors($account_number_array[$i])

    $spanchangVal = _StringBetween($page_source, '<span id="spanchangVal" class="srcovalue">', '</span>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($spanchangVal[0], "(") Then
        $spanchangVal_array = StringSplit($spanchangVal[0], "(", 1)
        $spanchangVal_array[1] = StringStripWS($spanchangVal_array[1], 8)
        $spanchangVal_array[2] = StringReplace($spanchangVal_array[2], ")", "")
        $spanchangVal_array[2] = StringStripWS($spanchangVal_array[2], 8)
    Else
        Global $spanchangVal_array[3]
        $spanchangVal_array[1] = "null"
        $spanchangVal_array[2] = "null"
    EndIf

    $strongCvalue = _StringBetween($page_source, '<span class="srcovalue"><strong id="strongCvalue">', '</strong></span>')
    If @error Then _errors($account_number_array[$i])

    $tdDHL = _StringBetween($page_source, '<td id="tdDHL" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($tdDHL[0], "/") Then
        $tdDHL_array = StringSplit($tdDHL[0], "/", 1)
    Else
        Global $tdDHL_array[3]
        $tdDHL_array[1] = "null"
        $tdDHL_array[2] = "null"
    EndIf

    $tdWAp = _StringBetween($page_source, '<td id="tdWAp" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])

    $tdTTQW = _StringBetween($page_source, '<td id="tdTTQW" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($tdTTQW[0], "/") Then
        $tdTTQW_array = StringSplit($tdTTQW[0], "/", 1)
    Else
        Global $tdTTQW_array[3]
        $tdTTQW_array[1] = "null"
        $tdTTQW_array[2] = "null"
    EndIf

    FileWriteLine($file_h, _
            $account_number_array[$i] & "," & _
            $spanCname[0] & "," & _
            $strongDate[0] & "," & _
            $spanchangVal_array[1] & "," & _
            $spanchangVal_array[2] & "," & _
            $strongCvalue[0] & "," & _
            $tdDHL_array[1] & "," & _
            $tdDHL_array[2] & "," & _
            $tdWAp[0] & "," & _
            $tdTTQW_array[1] & "," & _
            $tdTTQW_array[2] & "," & _

Next
FileClose($file_h)
MsgBox(0, "AutoIt", "Finished!")

Func _errors($pass)
    MsgBox(0, "AutoIt", "Error - not a valid stock number: " & $pass)
    ShellExecute("http://m.bseindia.com/StockReach.aspx?scripcd=" & $pass)
    FileClose($file_h)
    Exit
EndFunc

I try a small change just to split last column TTQ to TTQ and 2WQ
but it shows the error "parsing function all"
My System SpecsSystem Spec
30 Jun 2013   #18
UsernameIssues

W7 Pro SP1 64bit
 
 

try this:
Code:
Opt("TrayIconDebug", 1)

$S_running = "~stock-stuff~" ;name the script
If WinExists($S_running) Then Exit
AutoItWinSetTitle($S_running)

#include<Inet.au3>
#include <String.au3>


$whole_file = FileRead("symbol.txt")
$account_number_array = StringSplit($whole_file, @CRLF, 1)

$file_h = FileOpen("output.csv", 2)
If $file_h = -1 Then
    MsgBox(0, "AutoIt", "Could not open output file. Close Excel?")
    Exit
EndIf

FileWriteLine($file_h, _
        "Code" & "," & _
        "Name" & "," & _
        "Date" & "," & _
        "Change" & "," & _
        "% Change" & "," & _
        "Close" & "," & _
        "High" & "," & _
        "Low" & "," & _
        "Wtd. Avg Price" & "," & _
        "TTQ" & "," & _
        "2WQ")

For $i = 1 To $account_number_array[0]
    TrayTip("Pulling stock data ", "Stock " & $i & " of " & $account_number_array[0], 100)
    If $account_number_array[$i] = "" Then ContinueLoop ;skip empty lines

    $page_source = _INetGetSource("http://m.bseindia.com/StockReach.aspx?scripcd=" & $account_number_array[$i], 1)

    $spanCname = _StringBetween($page_source, '<span id="spanCname" class="companyname">', '</span><br>')
    If @error Then _errors($account_number_array[$i])

    $strongDate = _StringBetween($page_source, '<span id="strongDate">LTD- ', '</span>')
    If @error Then _errors($account_number_array[$i])

    $spanchangVal = _StringBetween($page_source, '<span id="spanchangVal" class="srcovalue">', '</span>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($spanchangVal[0], "(") Then
        $spanchangVal_array = StringSplit($spanchangVal[0], "(", 1)
        $spanchangVal_array[1] = StringStripWS($spanchangVal_array[1], 8)
        $spanchangVal_array[2] = StringReplace($spanchangVal_array[2], ")", "")
        $spanchangVal_array[2] = StringStripWS($spanchangVal_array[2], 8)
    Else
        Global $spanchangVal_array[3]
        $spanchangVal_array[1] = "null"
        $spanchangVal_array[2] = "null"
    EndIf

    $strongCvalue = _StringBetween($page_source, '<span class="srcovalue"><strong id="strongCvalue">', '</strong></span>')
    If @error Then _errors($account_number_array[$i])

    $tdDHL = _StringBetween($page_source, '<td id="tdDHL" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($tdDHL[0], "/") Then
        $tdDHL_array = StringSplit($tdDHL[0], "/", 1)
    Else
        Global $tdDHL_array[3]
        $tdDHL_array[1] = "null"
        $tdDHL_array[2] = "null"
    EndIf

    $tdWAp = _StringBetween($page_source, '<td id="tdWAp" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])

    $tdTTQW = _StringBetween($page_source, '<td id="tdTTQW" class="TTRow_right">', '</td>')
    If @error Then _errors($account_number_array[$i])
    If StringInStr($tdTTQW[0], "/") Then
        $tdTTQW_array = StringSplit($tdTTQW[0], "/", 1)
    Else
        Global $tdTTQW_array[3]
        $tdTTQW_array[1] = "null"
        $tdTTQW_array[2] = "null"
    EndIf

    FileWriteLine($file_h, _
            $account_number_array[$i] & "," & _
            $spanCname[0] & "," & _
            $strongDate[0] & "," & _
            $spanchangVal_array[1] & "," & _
            $spanchangVal_array[2] & "," & _
            $strongCvalue[0] & "," & _
            $tdDHL_array[1] & "," & _
            $tdDHL_array[2] & "," & _
            $tdWAp[0] & "," & _
            $tdTTQW_array[1] & "," & _
            $tdTTQW_array[2])
Next
FileClose($file_h)
MsgBox(0, "AutoIt", "Finished!")

Func _errors($pass)
    MsgBox(0, "AutoIt", "Error - not a valid stock number: " & $pass)
    ShellExecute("http://m.bseindia.com/StockReach.aspx?scripcd=" & $pass)
    FileClose($file_h)
    Exit
EndFunc   ;==>_errors
I changed this
$tdTTQW_array[2] & "," & _
to this
$tdTTQW_array[2])
My System SpecsSystem Spec
30 Jun 2013   #19
thanku

Windows 7 Ultimate x64
 
 

Thank you very much for the script and your support.
My System SpecsSystem Spec
30 Jun 2013   #20
UsernameIssues

W7 Pro SP1 64bit
 
 

You are welcome.

Let's hope that they don't change the website's layout :-)
My System SpecsSystem Spec
Reply

 Web site data to excel columns




Thread Tools Search this Thread
Search this Thread:

Advanced Search




Similar help and support threads
Thread Forum
how to divide row into 2 columns in excel
I need to create this table in Excel, How do I create table like I attached? How do I divide 2 row into 2 columns. Im using mac 2013 office. please help thx
Microsoft Office
Number data in 10 columns and 25 rows pasted from other source is text
..... and I want to convert the lot to 2 decimal place numbers. Excel has several methods, but none seem to work for conversion in bulk. Working on futher cells from a suitably formatted one does not work. Is the only way to solve my problem to manually enter the numbers elsewhere, clear the...
Microsoft Office
Can I set explorer columns for data directory within music library
As an example, The Itunes directory is in My Music. My Music is in a Music library, so the file details in explorer include album, #, etc. Within Itunes is the Apps directory and other data where "album name" doesn't make sense. I want it to display as a non-music directory. There doesn't...
General Discussion
Need to swap some data in Excel
Hi, I need to rearrange some data that are mistyped in an Excel sheet. Imagine two columns and many rows. There are some data that need to swap their places so it gets under the appropriate column. The data in this sheet are quite huge and I am thinking of a method like when clicking on those...
Microsoft Office
PDF File data into Excel Spreadsheet ?
I have WIN 7 with Excel 2003. Is it possible to transfer data contained in a .pdf file onto an Ecel spreadsheet ? If it is possible what is the procedure ?
Microsoft Office
Getting data from excel to firefox??
So... i have an excel doc with about 300 records all in a cell each, i want to get them all in to a specific field on a website in their own tab in firefox. any one know if this can be done? Thanks. D
General Discussion


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 13:13.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App