yStock - Realtime Charts & EOD downloads

[sivavkm]

Hi! Siva,

I needed your help with the web query...

Dear Amit,

Hmmm... yahoo is not giving any table-name or table-id property, so you will not get it by looking at "View Source", then you have to refer to it by absolute terms, by counting the number of table references in the source-code OR you may use excel for that.

About new web-query, you may find little difficulty in getting the table-ref properly, eventhough a one time activity. Either you may use the "View Source" option of your web-browser, and find the "table-name/table-id" in the source..... OR you may use the excel to record a macro, for a webquery to your target web address and check the "WebTables" property inthe macro code..( Excel => Data Menu=> Import External Data => New Web Query... ), this is the office 2003 menu option, may be slight changes in other versions.

Record an excel macro for your web-query, Excel => Tools Menu => Macro => Record New Macro, here is a sample macro code it generates then,

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;[COLOR="blue"][B]http://in.finance.yahoo.com/p?v&k=pf_4[/B][/COLOR]", Destination:=Range("A1"))
        .Name = "p?v&k=pf_4"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        [B][COLOR="Blue"].WebTables = "16"[/COLOR][/B]
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

The above webaddress is *my* portfolio in yahoo finance portal.
In yStock, to configure the above i will input
Web Address as, http://in.finance.yahoo.com/p?v&k=pf_4
Table Ref. as, 16

But in your case, i think it is ...
Web Address as, http://in.finance.yahoo.com/p?v&k=pf_3
Table Ref as, 21

Check it out.

Thanks & Regards
Siva

 

[vazu]

Dear sivavkm,

Marvellous work by you. I have installed in winxp with no difficulty.
I have logged into Yahoo finance with my ID before running yours. The volume sometimes shows last traded price. But for this bug, it is really good.
Shall I suggest the following as I am an oracle programmer?

can YOU get

the intra day value of the scrip in a column

average traded price of the scrip - i.e value devided by volume.

Can you provide downloading icon of the values in the cloumn to an excel sheet (like a csv file) at any point of time by stopping pinging or refreshing which will help me to analyse movements in excel.

Can you get futures quotes for NSE?

All these make yours great!

Regards
Vazu
reach me to: [email protected]

 

[gobatman2001]

Dear Amit,

Hmmm... yahoo is not giving any table-name or table-id property, so you will not get it by looking at "View Source", then you have to refer to it by absolute terms, by counting the number of table references in the source-code OR you may use excel for that.

The above webaddress is *my* portfolio in yahoo finance portal.
In yStock, to configure the above i will input
Web Address as, http://in.finance.yahoo.com/p?v&k=pf_4
Table Ref. as, 16
Siva

Hi! Siva,

Thanks a lot for your reply. As per the above method, I am getting an error message. Am attaching a screenshot for your reference, incase it helps.

Thanks,
Amit

Dear Amit,

But in your case, i think it is ...
Web Address as, http://in.finance.yahoo.com/p?v&k=pf_3
Table Ref as, 21

Check it out.

Thanks & Regards
Siva

As per this method, nothing happens. The screen does not get refreshed.

I am not too familiar with macros and have therfore not tried it out.

If you can come with a solution for YStock, please do let me know. I am most comfortable using YStock.


There is one more thing that I need your help on...

This is not a problem with YStock, but with Yahoo. Even though I click on "Remember my id on this computer" when I log in, the quotes displayed in YStock are always 15 minutes delayed. The reason is that Yahoo keeps prompting for my password every time I refresh a page. The settings that I have kept for Password Prompting is "Everyday". It should therefore prompt me for a password every 24 hours. Have gone through the Yahoo website help pages.

Have written to Yahoo about the above, but they could not come up with a solution. Are you aware of this problem and can you suggest a solution to this.

Without real time quotes, one would find little use for trading.

Once again, thanks a lot for what you are doing.


Regards,
Amit

 

[sivavkm]

Hi Amit,

In order to access a web-table you must have the table-ref property. Can you see your yahoo portfolio by clicking on this link http://in.finance.yahoo.com/p?v&k=pf_3 , without entering your yahoo password?

By looking at the screenshot of your error, hmmm... your table-ref pointing to wrong table, you have to correct that. Table-ref is the key, if that is wrong then... i dont know how to help you... please try creating an excel macro, for your yahoo portfolio using the excel-web-query method... it is really simple... with that you can get the table-ref property correctly.

Steps:-
1. Excel => Tools Menu => Macro => Record New Macro, click "OK" button
2. While the macro is recording, click on Data Menu=> Import External Data => New Web Query...
3. inthe New WebQuery dialog box, Paste http://in.finance.yahoo.com/p?v&k=pf_3 as the address, click "Go" button.
Now you can see your yahoo portfolio, inthat dialog box, may be excel will ask you for your yahoo password.
4. This step is the key step, After loading the page, you may notice several YELLO ARROW keys, each arros is a pointer to a table, you can click on that to select that table. So here, you should click on the *EXACT* table which is your portfolio, the arrow will change its color to "green", indicating that table is selected.
5. Click "Import" button
6. Another dialog box appears, click "OK"
7. Now, excel will try to import your portfolio, if everything went ok, you should be able to see your yahoo portfolio data in excel.
8. Let us stop recording the macro, you can do it by clicking on the stop button in macro toolbar, ________OR_______ Tools => Macro => Stop Recording menu option
9. Last step, Tools => Macro => Macros, click "Edit" button, you will see the macro code, look for .WebTables and its value. This is the value we are looking for

HTH
Regards
Siva

 

[murthymsr]

...........
Steps:-
.............
9. Last step, Tools => Macro => Macros, click "Edit" button, you will see the macro code, look for .WebTables and its value. This is the value we are looking for
.............
Siva

dear siva,

thanks for the detailed procedure you have given. i think you have office 2003.

my OS is WIN XP with Office XP. i have follwed your step by step procedure. the procedure indicated by you is valid for office XP also. all the tab names indicated by you are also valid for office XP.

while following the procedures, i observed as follows:

i have 6 portfolios (crazy, isn't it?). i tried your procedure for ALL my six portfolios. and i was successful in identifying the table nos for ALL the portfolios.

but the most surprising part is that ALL the portfolios are having the same table no: "11". did i make any mistake? do you have more than one portfolios? are the table nos you got different?

one more point. i tried to make these trials (just 30 mins back, when market is open) through yahoo india. it was nagging me for password confirmation every time. so logged in to yahoo USA (got all of them in one go) and made the above observations.

request your comments on above observations.

thanks in advance.

MurthyMSR

 

[sivavkm]

...but the most surprising part is that ALL the portfolios are having the same table no: "11". did i make any mistake? do you have more than one portfolios? are the table nos you got different?

I have multiple portfolios in yahoo finance portal, and i am getting table no: "16" for all my portfolios , and it is working from yStock. I am in middle-east, using AvantBrowser as my browser, any connection !!!???

Me too, excel is asking for the yahoo login info always, why? no idea!

BTW, have you got it working in yStock, i mean your yahoo portfolios?

Regards
Siva

 

[murthymsr]

I have multiple portfolios in yahoo finance portal, and i am getting table no: "16" for all my portfolios , and it is working from yStock. I am in middle-east, using AvantBrowser as my browser, any connection !!!???

Me too, excel is asking for the yahoo login info always, why? no idea!

BTW, have you got it working in yStock, i mean your yahoo portfolios?

Regards
Siva

1) may be yahoo india bandwidth, which you may not agree. i am getting the nag only since a week, and never earlier. may be your program is becoming popular.

2) with yahoo USA, the problem has so far never occured.

3) out of my 6 portfolio's 4 are working in yStock. the remaining two were giving error, similar to what mit awas getting, screenshot of which was posted at:
http://www.traderji.com/56189-post105.html

4) but, but unfortunately in none of the 6 corresponding market watches set, the auto update function is working.

5) the table nos variation between we too may be (a wild guess) because of different yahoo servers we are browsing into, the local web ssite's number of ads & other local info may be at variance from region to region. what do you feel of this possibility?

i am not clear if this varied performance has something to do with the comptibility of your program with office XP. if you have not on xp sp far, you may run on xp and advice on points reported.

thanks for the prompt response as always.
MurthyMSR

 

[gobatman2001]

Hi Amit,

Steps:-
1. Excel => Tools Menu => Macro => Record New Macro, click "OK" button

9. Last step, Tools => Macro => Macros, click "Edit" button, you will see the macro code, look for .WebTables and its value. This is the value we are looking for

HTH
Regards
Siva

Hi! Siva,

Thanks a TON for the detailed explanation.

Really appreciate it!

Regards,
Amit

 

[gobatman2001]

Hi! Siva,

Wanted to share this with you ...

When I download NSE EOD using YStock, there are 935 symbols that get downloaded.

When I download the same thing using Satya's Data Extractor, there are 942 symbols that get downloaded.

You can download the files from the following link :
http://rapidshare.de/files/30867220/NSE_Data.zip.html

Regards,
Amit

 

[sivavkm]

Hi! Siva,

Wanted to share this with you ...

When I download NSE EOD using YStock, there are 935 symbols that get downloaded.

When I download the same thing using Satya's Data Extractor, there are 942 symbols that get downloaded.

You can download the files from the following link :
http://rapidshare.de/files/30867220/NSE_Data.zip.html

Regards,
Amit

Dear Amit,

Good point to debate.

yStock downloader will include records where NSEseries equals "EQ" or "BE" ONLY, this is to avoid duplicate data (suggested by karthikmarar), it will filter-out all other records.

Most of the TA(MetaStock, Amibroker, FCharts, FiboTrader...) programs not considering "series" data, so during importing data to your TA for a given date, if there are more than one record for a symbol it will overwrite the previous data (i verified it for AmiBroker)

For eg: symbol TITAN, there are two records, which one you would like to import to your TA?
SYMBOL,SERIES,OPEN,HIGH,LOW,CLOSE,LAST,PREVCLOSE,TOTTRDQTY,TOTTRDVAL,TIMESTAMP
TITAN,EQ,775,779.7,754.25,758.95,755.25,767.55,400973,308312176.2,25-Aug-06
TITAN,N1,221.6,222.05,221.6,222.05,222.05,222.7,4,886.85,25-Aug-06

I am attaching a zip file, NSE EOD as on 25-8-2006, downloaded directly from NSE site(not thru yStock). After filtering it will show 935 rows only, check it out yourself.

Thanks & Regards
Siva