Realtime data NOW,ODIN,TradeTiger,Google,Yahoo > AmiBroker, Fcharts, Qtstalker

[josh1]

Google Finance to Excel ........

Thanks to Ramdas for his site - > http://niftyfno.forums-free.com/google-realtime-intraday-backfill-data-t40.html ....... where he has given all the URLs of Google from which data can be obtained.

Google Realtime feed can be obtained in three ways. I shall discuss them later. RTG3.xlsm fetches Google RT feed from this URL:-

"http://finance.google.com/finance/info?client=ig&q=NSE:IDFC,NSE:ACC"
you can just add more script ,NSE:ScriptName

I compared the time lag in Realtime quotes from each source and found that the above URL gives quotes within a time lag of 2-3 seconds which I feel is quite acceptable. There is no limit to the number of scrips that can be monitored. Realtime Quotes of other exchanges can also be obtained.

There are three down sides -
1. FnO quotes are not available.
2. Volume is not available through this URL
3. Data comes in text format. It has to be processed. Hence there is some processing overhead. I have kept the time interval for refreshing at 5 seconds. Users having lot of scrips may find that Copy/paste is not working when Google RT feed is running. In that case, they will have to increase the refresh interval.

 

[josh1]

Realtime data feed to Open Office Calc......

Open Office, to my knowledge, does not support RTD function of Excel as yet. Hence getting realtime feed in Open Office Calc from NOW/NEST/ODIN/TT is not possible. I have not installed Open Office in Windows. Those who have it may give it a try and revert here for everybody's benefit. I tried installing NOW under WINE in Ubuntu Linux but failed. Hence, the only option remaining is to get Realtime feed from Yahoo and Google. As stated earlier, Yahoo is late by 20 minutes so Google is a better option.

Open Office Starbasic has quite a different syntax for fetching quotes from internet. I am still not clear how to translate the VBA code in Starbasic. If any readers of this post are conversant with it, they can see the code and develop speadsheet to get Google Realtime feed in Calc. I am willing to help them so that the development can be done in couple of days. If that development is done then we can fetch Google Realtime feed in Linux and Qtstalker easily.

 

[DataRam]

As stated earlier, Yahoo is late by 20 minutes so Google is a better option.

Hi Josh1 , when we login in Yahoo.com then Yahoo gives RT quotes which are not delayed. So can there be some option of integrating Yahoo login within the utility. I used myQuotes from Volume Digger and that utility had an option to login in Yahoo site through it.

This is just an opinion from a non-techie person so please don't mind if I have gone way too far in expressing it without knowing the pros & cons.

Regards.

 

[josh1]

Hi Josh1 , when we login in Yahoo.com then Yahoo gives RT quotes which are not delayed. So can there be some option of integrating Yahoo login within the utility. I used myQuotes from Volume Digger and that utility had an option to login in Yahoo site through it.

This is just an opinion from a non-techie person so please don't mind if I have gone way too far in expressing it without knowing the pros & cons.

Regards.

Just open your browser and login into Yahoo. The utility will do rest of the work. Yahoo does not give RT quotes. Login is done to ensure that Yahoo does not block you from accessing their site.

 

[DataRam]

Just open your browser and login into Yahoo. The utility will do rest of the work. Yahoo does not give RT quotes. Login is done to ensure that Yahoo does not block you from accessing their site.

Oh !!

I knew that somehow I am expressing something which is out of my domain.
So people who use Manshi RT Yahoo feed are using 15-20 minutes delayed quotes ? This means that Rs. 400 monthly is too much as subscription fees [ this is personally my opinion ] .

Regards.

 

[josh1]

Getting Realtime feed to AmiBroker........

Once you start getting realtime quotes in Excel, next step is to get them in AmiBroker and see the charts moving.
Here is a snapshot of RT3.xlsm. Left side is for NOW/Nest and right side is for Yahoo Quotes.

You see all #N/A in the left part because NOW was not opened at the time. Should this happen to you, close excel and open NOW/Nest first.
We are taking date from our own system since trading software does not give it. Check that you are pasting items strictly as per column headings given. You can add/delete/modify scrips as per your liking. There is no limit to the number of scrips subject to the limit set by NOW/Nest which seems to be 55.

Procedure for adding scrips is same for NOW and Nest. See the picture below-

Use Trading Symbol instead of Symbol and there is no need of Strike Price. Arrange the columns in the order as given in Excel- Trading Symbol, Last Trading time, Last Traded Price, Last Traded Qty, Open Interest, Symbol.

AmiBroker Database Path means path where you have created your database for Realtime feed. Remember, you have to create database in AmiBroker using File - New-- Database Menu. In my case it is on "H" drive. The drive/path should be accessible. Otherwise AmiBroker will give you error to select correct database. Database settings are shown here

Those who do not have any of the trading software mentioned in this thread can use Yahoo feed. Just select "No" to NOW Realtime Feed. Time offfset should be changed from 9:30:00 to 10:30:00 once in six months. There is no limit on number of scrips. However in case of Yahoo, you will have to copy the formula for "IST" and "Time" against each scrip. This is required to get correct trading time in AmiBroker.

contd ....

 

[josh1]

Realtime feed to AmiBroker............. contd.

Here is a snapshot of RTG3.xlsm

Left part is for NOW/Nest/ODIN/TT and right part for Google Realtime feed. Those who do not have AmiBroker can just remove the AmiBroker Database Path.
If you say "No" to Now realtime feed, it will not be pushed to AmiBroker. similarly for Google feed.

URL from which data feed is obtained, is placed in cell "J1". As per my observation, quotes are delayed by maximum 3 seconds.

Google gives Last Trade Time in Indian Standard Time(IST), so adjustment for time zone is not required. However, Trading Time start and end is provided since it keeps on giving quotes upto 5:00 PM. The utility will not push quotes before start time and after end time to AmiBroker.

Click on "Start RT Data" to start Realtime feed to AmiBroker. This feed is pushed to AmiBroker every 5 seconds if Google is required. There is no limit to number of scrips.

Known issues:-
1. Google does not give Volume in this URL.
2. Data comes in text and Excel puts it in different rows one below the other. Getting the data in one row incurs processing overhead. It involves copying data from one location to other. Therefore, Copy/paste function of Windows stops working. In my case, windows is in Virtualbox. I found that copy/paste stops functioning in host linux also. You have to copy/paste quickly between the five second time interval. If that is not possible, and you want to do other work, increase the refresh interval in the VBA code. We will see later how to do that.

 

[josh1]

Advantages of the utility ..........

1. I get the charts updated as soon as quotes are displayed in trading software. There is delay of fraction of a second which is quite acceptable. The charts are matching with my trading software.

2. AmiBroker is automatically opened when Start button is pressed.

3. We are using OLE Automation capbility of AmiBroker. Hence Operation is easy. Not much tweaking is required in Excel Sheet. The code is also small comprising of just two pages.

4. New scrips added to Excel get automatically added to AmiBroker.

5. The combination - My Utility, NOW and Zerodha is the cheapest option for small traders if daily up to 3 orders are placed.

Known issue with the utility .........

1. We are taking tick data every three seconds from NOW/Nest through this utility so whatever trades happened during the gap is missed. If we have to capture cumulative volume, we will have to push data for every tick into AmiBroker which will bring our machine to halt. None of the data feeding applications are doing that.

Solution:- Create and Array in VBA to hold the volumes of all scrips with initial value 0 (zero). Take "Volume traded today" from NOW/Nest instead of "Last Traded Qty". Subtract the values in array from new values every time, send the difference to AmiBroker and thereafter push the new values into the array. This will give the volume for the refresh period (three seconds).

This involves manipulating arrays in VBA. I am not conversant with that yet. If any of the readers is good at VBA and willing to help, we can improve this utility faster. Otherwise users will have to wait till I am able to build it.

2. There is a constant disk read/write for creating CSV file that is imported by AmiBroker.

This is solved by creating a RAM disk as explained in post #2.

 

[josh1]

Realtime feed to FCharts..........

FCharts is another application for stock charting. There are two versions of this application. 1. FChartSE is free for all users and 2. FChartPRO is paid versions. I had used the free version about two years ago. I found some discrepancies in weekly charts. That is why and also because I had money to spend, I bought AmiBroker. I installed the latest free version about a month ago. It seems to have improved a lot and added some excellent facilities in two years. New Traders are advised to use this application.

Uploaded file NSE-NOW-RT-To-FC.xlsm. Works out of the box with FCHARTSE the free charting software.

Those who are not getting Last Traded time in data feed(diet ODIN) will have to click on "Start" Worksheet to get current time. All others just have to start data feed application like NOW or NEST and get realtime feed to FChartSE. Link is here. FchartSE is very easy for intraday charts.

http://www.4shared.com/file/bQgiuIjq...-RT-To-FC.html

You will have to login into 4shared.com to download.

http://dc523.4shared.com/download-to...91738-934206e3

Steps-
1. Open NOW or Nest or which ever software
2. Open NSE-NOW-RT-TO-FC.xlsm and check whether rates are changing. (Trust Centre settings to be done as given earlier in this thread)
3. Open FChartSE
4. Click on Intraday and select Excel as source.
5. Click on Refil List and It will show the name of excel file.
6. Column settings as given in the image. They are self explanatory.
7. 1st Data Row in our case is 7.
You can see one minute bars getting updated live in FCharts.

FCharts can be used with the new RT3.xlsm and RTG3.xlsm also. We will have to add Date Column since it needs that. Do not add any column. Use the "Symbol" column instead. Erase its contents and type "=Today()" without quotes against each scrip in that column.

We can get Yahoo and Google Realtime feed also in FCharts.
Just open more instances of FCharts and map the intraday columns. The database will ofcourse be different.

The time may not update in case of ODIN/TT users. They should give option "No" for Realtime feed to AmiBroker.
Click on "Start RT Data". That should update time every three seconds.

 

[josh1]

Realtime data feed to Qtstalker.........

QtStalker is an open source free charting software available in Linux. It can be installed in Ubuntu from "Ubuntu Software Centre" or "Synaptic".

NOW,ODIN,TradeTiger do not run under Linux to my knowledge. Open Office does not support RTD yet. How do we get their output to Qtstalker?

We can install Windows as a guest in Virtualbox or VMware under Linux or
we can install Ubuntu Linux as a guest in Virtualbox or VMware under Windows.
Qtstalker can be installed in Linux.

The above applications can be installed in Windows and run from there.

Virtualbox offers facility to share a folder on the Host OS with Guest OS.
VMWare also offers facility to Guest OS to access folder on the Host OS via SAMBA network.
Suppose this folder path is "H:\RT".

Open Visual Basic in Developer Tab and go to Module1. Find this line in the VBA code in subroutine MakeCSV().->
FileName = "R:\MyCSVG.txt" 'This file is used to write quotes

Change this to -> FileName = "H:\RT\MyCSVG.txt" 'This file is used to write quotes

QtStalker has an ascii plugin to import quotes from text file at fixed interval of 1 minute. This ascii plugin can be directed to the MyCSVG.txt file for importing quotes.
The procedure is given in Qtstalker documentation for the plugin.

As for Google and Yahoo data feed, the same procedure can be used until we are able to translate the application in Open Office Starbasic. These two feeds can be obtained in Qtstalker via Open Office or any other language like C++, Python, PERL in Linux without the need of Windows.