indiTraders - Forum for the Active Indian Trader  

Go Back   indiTraders - Forum for the Active Indian Trader > Tools > Data

Data Share Data & Discuss Data Sources

Reply
 
Bookmark and Share LinkBack Thread Tools Display Modes
  #11 (permalink)  
Old 28-11-2016, 11:46 AM
rsi rsi is offline
indiTraders VIP
 
Join Date: Mar 2009
Posts: 863
Thanks: 924
Thanked 3,657 Times in 796 Posts
rsi has a reputation beyond repute
rsi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond repute
Default

Originally Posted by no1lives4ever View Post
Why not use a bhavcopy downloader?

Instead of relying upon some third party software, why can't we be self sufficient?

Exchanges are offering EOD data for free. If we can modify it by ourselves, using simple solutions, then we will be independent.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following User Says Thank You to rsi For This Useful Post:
  #12 (permalink)  
Old 28-11-2016, 12:42 PM
no1lives4ever no1lives4ever is online now
Newbie
 
Join Date: Jul 2009
Posts: 3,231
Thanks: 18,451
Thanked 5,436 Times in 2,308 Posts
no1lives4ever has a reputation beyond repute
no1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond reputeno1lives4ever has a reputation beyond repute
Default

Originally Posted by rsi View Post
Instead of relying upon some third party software, why can't we be self sufficient?

Exchanges are offering EOD data for free. If we can modify it by ourselves, using simple solutions, then we will be independent.

You have a point here..

As a programmer, I always write code for these things on my own and never depend on third party tools.

As such manipulating the bhavcopy files into a format that is suitable for import into a charting app as continuous contracts is not as simple as it may seem and can take a bit of code. Even if you do this with excel, it will require some macro code. But excel would be easier to do than if you are writing code in some other platform like matlab, r or python.

IMO if you cant write code and if you dont want to depend on code written by others, then you will need to either learn to code yourself or hire programmers to write custom code for you.

-- no1lives4ever
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #13 (permalink)  
Old 28-11-2016, 01:21 PM
rsi rsi is offline
indiTraders VIP
 
Join Date: Mar 2009
Posts: 863
Thanks: 924
Thanked 3,657 Times in 796 Posts
rsi has a reputation beyond repute
rsi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond repute
Default

Originally Posted by no1lives4ever View Post

Even if you do this with excel, it will require some macro code. But excel would be easier to do than if you are writing code in some other platform like matlab, r or python.

IMO if you cant write code and if you dont want to depend on code written by others, then you will need to either learn to code yourself or hire programmers to write custom code for you.

-- no1lives4ever

Excel macros would be better. Because

1. It would be easy to learn

2. I do not expect much changes to basic excel macro codes anytime in future.

3. I can retain the file in excel format itself, which can be used in any charting software. Pardon my ignorance about matlab and python languages. These software languages are changing quite often.

4. Basic software program like excel will have much longer shelf life than newer software program/languages. This is simply because of wide user base and economies of scale.

So please post excel macros. Perhaps a small video like the one Sunshine posted sometime earlier in another thread would be quite helpful.

Thanks in advance

R. S. Iyer
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 4 Users Say Thank You to rsi For This Useful Post:
  #14 (permalink)  
Old 28-11-2016, 07:56 PM
Darshiit's Avatar
Darshiit Darshiit is offline
indiTraders VIP
 
Join Date: Mar 2009
Posts: 949
Thanks: 9,108
Thanked 7,147 Times in 1,844 Posts
Darshiit has a reputation beyond reputeDarshiit has a reputation beyond repute
Darshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond reputeDarshiit has a reputation beyond repute
Default

Excel macros are easy to learn ,..

If you dont know exact programing syntex for anything , just simply hit record button and start doing modifications you want to do in Excel file. After finishing your formatting job. click stop recording.

then Go to macros or hit alt+f11 ,.. that will take you inside VB code.

Go through the code ,.. its easy to understand...

Get some examples on Net ,... a lot of youtube playlists out there to teach you VBA...

Regards,..
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 3 Users Say Thank You to Darshiit For This Useful Post:
  #15 (permalink)  
Old 28-11-2016, 09:55 PM
asnavale asnavale is offline
indiTraders VIP
 
Join Date: Jun 2014
Posts: 270
Thanks: 609
Thanked 781 Times in 263 Posts
asnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really nice
Default

Originally Posted by rsi View Post
Exactly. How to get it?

Thanks in advance

You have already downloaded the two files from BSE. Open both in Excel. Copy the Delvery data column from the Excel sheet and then right click in the Bhavcopy sheet in the column where you want to paste it. Select "Paste special" and choose "Values" and paste. That's all.

-Anant
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 2 Users Say Thank You to asnavale For This Useful Post:
  #16 (permalink)  
Old 28-11-2016, 10:24 PM
asnavale asnavale is offline
indiTraders VIP
 
Join Date: Jun 2014
Posts: 270
Thanks: 609
Thanked 781 Times in 263 Posts
asnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really niceasnavale is just really nice
Default

Originally Posted by rsi View Post
One more problem in addition to what is described earlier.

Please download the F&O Bhavcopy from NSE or bhavcopy from MCX or NCDEX

They are giving name of the stock in one column and the expiry date of contract in another column.

Now how to assign symbols in the following manner

For contracts expiring in December 2016, say NIFTY, as NIFTY-1

For contracts expiring in January 2017, say NIFTY, as NIFTY-2

For contracts expiring in February 2017, say NIFTY, as NIFTY-3 etc.?

Hi Iyer,

Let us say in F&O Bhavcopy Column A contains the scrip name, Column B contains Expiry Date, and you want the combination in Column K. So, We should have it like this:

Column A, Cell 5: NIFTY (Scrip Name)
Column B, Cell 5: 29 Dec 2016 (Expiry Date)
Column K, Cell 5: NIFTY-I (Final result)

In column K, Cell 5 Type the following:

=CONCATENATE(TRIM(A5),"-I")

Copy this formula in all the cells where you want the result.

-Anant
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 4 Users Say Thank You to asnavale For This Useful Post:
  #17 (permalink)  
Old 08-12-2016, 10:47 AM
sr114's Avatar
sr114 sr114 is offline
Regulars
 
Join Date: Aug 2010
Location: INDIA
Posts: 340
Thanks: 362
Thanked 622 Times in 233 Posts
sr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the rough
Default

Originally Posted by rsi View Post
One more problem in addition to what is described earlier.

Please download the F&O Bhavcopy from NSE or bhavcopy from MCX or NCDEX

They are giving name of the stock in one column and the expiry date of contract in another column.

Now how to assign symbols in the following manner

For contracts expiring in December 2016, say NIFTY, as NIFTY-1

For contracts expiring in January 2017, say NIFTY, as NIFTY-2

For contracts expiring in February 2017, say NIFTY, as NIFTY-3 etc.?

R for EOD Data Transformation

see this link for the answer to ur query. the numbering will be in Roman not numerals.

rgds

Last edited by sr114; 08-12-2016 at 11:07 AM.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 5 Users Say Thank You to sr114 For This Useful Post:
  #18 (permalink)  
Old 09-12-2016, 05:26 PM
rsi rsi is offline
indiTraders VIP
 
Join Date: Mar 2009
Posts: 863
Thanks: 924
Thanked 3,657 Times in 796 Posts
rsi has a reputation beyond repute
rsi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond reputersi has a reputation beyond repute
Default

Thanks Subroto

But how to use this formula in excel? Spoon feeding required
Thanks in advance
R. S. Iyer
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 2 Users Say Thank You to rsi For This Useful Post:
  #19 (permalink)  
Old 09-12-2016, 09:21 PM
sr114's Avatar
sr114 sr114 is offline
Regulars
 
Join Date: Aug 2010
Location: INDIA
Posts: 340
Thanks: 362
Thanked 622 Times in 233 Posts
sr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the rough
Default

Originally Posted by rsi View Post
Thanks Subroto

But how to use this formula in excel? Spoon feeding required
Thanks in advance
R. S. Iyer

way to convert raw fno data

Open excel and try "ALT+F11" to open the vba editor. put the vba code there and save the sheet with an appropriate name to use it later.

Get the current day data from this link
https://nseindia.com/archives/fo/bhav/fo091216.zip

Archieve data
[https://nseindia.com/products/conten...chieve_fo.htm]


converting the filtered data

Code:
Sub Transform()

Range("P2").Formula = "=IF($B2&""""&$C2=$B1&""""&$C1,$P1,1+$P1*($B2=$B1))"
Range("P2", "P" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Range("Q2").Formula = "=TRIM($B2)&"" ""&ROMAN($P2)&REPT("" ""&$D2&"" "" &$E2,OR($A2=""OPTIDX"",$A2=""OPTSTK""))"
Range("Q2", "Q" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
   
    Sheets(1).Select
    Range("O1:O500000").Copy
    Sheets(2).Select
    Range("A1:A500000").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("Q1", "Q" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
    Sheets(2).Select
    Range("B1:B500000").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("F1:I500000").Copy
    Sheets(2).Select
    Range("C1:F500000").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("K1:K500000").Copy
    Sheets(2).Select
    Range("G1:G500000").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("M1:M500000").Copy
    Sheets(2).Select
    Range("H1:H500000").Select
    ActiveSheet.Paste
    
    
    [A1].Value = "DATE"
    [B1].Value = "SYMBOL"
    [C1].Value = "OPEN"
    [D1].Value = "HIGH"
    [E1].Value = "LOW"
    [F1].Value = "CLOSE"
    [G1].Value = "VOLUME"
    [H1].Value = "O.INT"
   
    Sheets(2).Select
    Columns("J:K").EntireColumn.Delete
 
    Application.DisplayAlerts = False
    Dim Name As String
    Dim FileName As String
    'Name = ActiveSheet.Name & Format(Now(), "mm.dd.yyyy")
    'FileName = ThisWorkbook.Path & "\" & ActiveSheet.Name & " " &     Format(Date, "mm.dd.yy") & ".csv"
   
   'ActiveWorkbook.SaveAs FileName:= _
        'ThisWorkbook.Path & "\" & "" & _
        'Format(Date, "ddmmyyyy") & ".csv", FileFormat:=xlCSV, CreateBackup:= _
        'False
      sFilename = "FO"
        
      ActiveWorkbook.SaveAs FileName:= _
        ThisWorkbook.Path & "\" & sFilename & _
        Format(Range("A2"), "ddmmyyyy") & ".csv", FileFormat:=xlCSV, CreateBackup:= _
        False
        
        MsgBox "File " & Name & " has been Created and Saved under:  " & FileName, , "Copy & Save Report"
  Worksheets("Sheet1").Activate
  ActiveWorkbook.Close
  Application.DisplayAlerts = True
End Sub
put the vba code in ur macro editor and save the excel sheet as macro enabled [ withe the extension .xlm]

copy the data in the macro sheet and execute the macro - ur data will be converted with roman numerals according to the month of expiry , with current month data bearing I and last month bearing III .

try experimenting

regards
Subroto
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 3 Users Say Thank You to sr114 For This Useful Post:
  #20 (permalink)  
Old 09-12-2016, 09:27 PM
sr114's Avatar
sr114 sr114 is offline
Regulars
 
Join Date: Aug 2010
Location: INDIA
Posts: 340
Thanks: 362
Thanked 622 Times in 233 Posts
sr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the roughsr114 is a jewel in the rough
Default

RSI

for ur query from this thread, BSE delivers eod data and gross deliverable data with the code of the scrip.

please combine the eod data and gross delivery data with the common filed being the code numbers.

search some excel vba code [ google it] to get the combined data.

I personally use R script to combine the eod data and gross delivery data , both for NSE and BSE .

rgds
Subroto

Last edited by sr114; 09-12-2016 at 09:30 PM.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
The Following 4 Users Say Thank You to sr114 For This Useful Post:
Reply

indiTraders - Forum for the Active Indian Trader > Tools > Data


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are On



All times are GMT +5.5. The time now is 07:59 AM.


vBulletin Copyright by vBulletin

Content Relevant URLs by vBSEO 3.3.2