Scribd
Upload
171 views6 pages

VBA Script for Fetching Option Chain

The VBA code retrieves option chain data from the NSE website and populates it in an Excel sheet. It uses the msxml2.xmlhttp object to make an API call and parse the JSON response. Based on the selected script, it determines the API URL and headers. It retrieves the strike prices and expiry dates arrays from the response. It then loops through the data and populates the relevant fields for CE and PE in an array, which is written to the sheet. It also handles errors by retrying the request and selecting a default browser to open if needed.

Uploaded by

Industry Standard Structural Design
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
171 views6 pages

VBA Script for Fetching Option Chain

The VBA code retrieves option chain data from the NSE website and populates it in an Excel sheet. It uses the msxml2.xmlhttp object to make an API call and parse the JSON response. Based on the selected script, it determines the API URL and headers. It retrieves the strike prices and expiry dates arrays from the response. It then loops through the data and populates the relevant fields for CE and PE in an array, which is written to the sheet. It also handles errors by retrying the request and selecting a default browser to open if needed.

Uploaded by

Industry Standard Structural Design
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 6

_________________________________________________

Sheet 1
_________________________________________________

Private Sub CommandButton1_Click()


Call fetchOptionChain
End Sub
_________________________________________________

ThisWorkbook
_________________________________________________

Private Sub Workbook_Open()


Sheets("Sheet1").Activate

Call pullOptionChain

End Sub

_________________________________________________

Module1
_________________________________________________

Option Explicit
Sub pullOptionChain()

Dim Json As Object


Dim webURL As String, mainString, subString, expiryDateStr, selectedScrip
Dim aMyArray() As Variant
Dim i, j, k As Integer
Dim strikePricesArr() As Variant, expiryDatesArr()

selectedScrip = Sheets("Sheet1").[Link]

If selectedScrip = "" Then


selectedScrip = "NIFTY"
End If

webURL = "[Link] &


selectedScrip
subString = "Resource not found"

specific_browser ("[Link]

FetchAgain:

With CreateObject("[Link]")

[Link] (Now + TimeValue("[Link]"))

specific_browser (webURL)

.Open "GET", webURL, False

.setRequestHeader "Content-Type", "application/json"


.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 [Link] GMT"
.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64)
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36"

.send

mainString = .responseText

If InStr(mainString, subString) <> 0 Then

specific_browser (webURL)
[Link] (Now + TimeValue("[Link]"))
GoTo FetchAgain

Else

Set Json = [Link](mainString)

End If

End With

ReDim strikePricesArr(Json("records")("strikePrices").Count)

For i = 1 To Json("records")("strikePrices").Count
strikePricesArr(i) = Json("records")("strikePrices")(i)
Next

ReDim expiryDatesArr(Json("records")("expiryDates").Count)

For i = 1 To Json("records")("expiryDates").Count
expiryDatesArr(i) = Json("records")("expiryDates")(i)
Next

Worksheets("Sheet1").Activate
Range("A4:K999").ClearContents

expiryDateStr = expiryDatesArr(1)
Range("A1").Value = "Fetched on: " & Json("records")("timestamp")
Range("F2").Value = Json("records")("underlyingValue")

j = 0
k = 1

aMyArray = Range("A4:K999")
[Link] = False

For i = 1 To Json("records")("data").Count

If Json("records")("data")(i)("expiryDate") = expiryDateStr Then

j = j + 1

' If Abs(Json("records")("data")(i)("strikePrice") - Json("records")


("underlyingValue")) < 400 Then

If IsObject(Json("records")("data")(i)("CE")) Then
aMyArray(j, k) = Json("records")("data")(i)("CE")("openInterest")
aMyArray(j, k + 1) = Format(Json("records")("data")(i)("CE")
("changeinOpenInterest"), "#.00")
aMyArray(j, k + 2) = Json("records")("data")(i)("CE")("totalTradedVolume")
aMyArray(j, k + 3) = Json("records")("data")(i)("CE")("impliedVolatility")
aMyArray(j, k + 4) = Json("records")("data")(i)("CE")("lastPrice")
aMyArray(j, k + 5) = Json("records")("data")(i)("strikePrice")
Else
aMyArray(j, k) = ""
aMyArray(j, k + 1) = ""
aMyArray(j, k + 2) = ""
aMyArray(j, k + 3) = ""
aMyArray(j, k + 4) = ""
aMyArray(j, k + 5) = ""
End If

If IsObject(Json("records")("data")(i)("PE")) Then
aMyArray(j, k + 6) = Json("records")("data")(i)("PE")("lastPrice")
aMyArray(j, k + 7) = Json("records")("data")(i)("PE")("impliedVolatility")
aMyArray(j, k + 8) = Json("records")("data")(i)("PE")("totalTradedVolume")
aMyArray(j, k + 9) = Format(Json("records")("data")(i)("PE")
("changeinOpenInterest"), "#.00")
aMyArray(j, k + 10) = Json("records")("data")(i)("PE")("openInterest")
Else
aMyArray(j, k + 6) = ""
aMyArray(j, k + 7) = ""
aMyArray(j, k + 8) = ""
aMyArray(j, k + 9) = ""
aMyArray(j, k + 10) = ""
End If

End If

Next i

Range("A4:K" & j + 3) = aMyArray


Columns("A:K").AutoFit
Columns("A:K").VerticalAlignment = xlCenter
Columns("A:K").HorizontalAlignment = xlCenter
Cells(1, 1).Select

[Link] = True

End Sub
_________________________________________________

Module2
_________________________________________________

Option Explicit
Sub fetchOptionChain()

Dim Json As Object


Dim webURL As String, mainString, subString, expiryDateStr, selectedScrip
Dim aMyArray() As Variant
Dim i, j, k As Integer
Dim emptyInfo As Integer
Dim strikePricesArr() As Variant, expiryDatesArr()

selectedScrip = Sheets("Sheet1").[Link]

selectedScrip = Replace(selectedScrip, "&", "%26")


selectedScrip = Replace(selectedScrip, " ", "")

If selectedScrip = "" Then


selectedScrip = "NIFTY"
End If

If selectedScrip = "NIFTY" Or selectedScrip = "BANKNIFTY" Or selectedScrip =


"FINNIFTY" Then
webURL = "[Link] &
selectedScrip
Else
webURL = "[Link] &
selectedScrip
End If

subString = "Resource not found"

specific_browser ("[Link]

FetchAgain:

With CreateObject("[Link]")

[Link] (Now + TimeValue("[Link]"))

specific_browser (webURL)

.Open "GET", webURL, False

.setRequestHeader "Content-Type", "application/json"


.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 [Link] GMT"
.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64)
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36"

.send

mainString = .responseText

If InStr(mainString, subString) <> 0 Then

specific_browser (webURL)
[Link] (Now + TimeValue("[Link]"))
GoTo FetchAgain

GoTo FetchAgain

Else

Set Json = [Link](mainString)

End If

End With

ReDim strikePricesArr(Json("records")("strikePrices").Count)

For i = 1 To Json("records")("strikePrices").Count
strikePricesArr(i) = Json("records")("strikePrices")(i)
Next
ReDim expiryDatesArr(Json("records")("expiryDates").Count)

For i = 1 To Json("records")("expiryDates").Count
expiryDatesArr(i) = Json("records")("expiryDates")(i)
Next

Worksheets("Sheet1").Activate
Range("A4:K999").ClearContents

expiryDateStr = expiryDatesArr(1)
Range("A1").Value = "Fetched on: " & Json("records")("timestamp")
Range("F2").Value = Json("records")("underlyingValue")

j = 0
k = 1

aMyArray = Range("A4:K999")
[Link] = False

For i = 1 To Json("records")("data").Count

If Json("records")("data")(i)("expiryDate") = expiryDateStr Then

emptyInfo = 0
j = j + 1

' If Abs(Json("records")("data")(i)("strikePrice") - Json("records")


("underlyingValue")) < 400 Then

If IsObject(Json("records")("data")(i)("CE")) Then
aMyArray(j, k) = Json("records")("data")(i)("CE")("openInterest")
aMyArray(j, k + 1) = Format(Json("records")("data")(i)("CE")
("changeinOpenInterest"), "#.00")
aMyArray(j, k + 2) = Json("records")("data")(i)("CE")("totalTradedVolume")
aMyArray(j, k + 3) = Json("records")("data")(i)("CE")("impliedVolatility")
aMyArray(j, k + 4) = Json("records")("data")(i)("CE")("lastPrice")
aMyArray(j, k + 5) = Json("records")("data")(i)("strikePrice")
Else
emptyInfo = 1
aMyArray(j, k) = ""
aMyArray(j, k + 1) = ""
aMyArray(j, k + 2) = ""
aMyArray(j, k + 3) = ""
aMyArray(j, k + 4) = ""
aMyArray(j, k + 5) = ""
End If

If IsObject(Json("records")("data")(i)("PE")) Then
If emptyInfo = 0 Then
aMyArray(j, k + 6) = Json("records")("data")(i)("PE")("lastPrice")
aMyArray(j, k + 7) = Json("records")("data")(i)("PE")("impliedVolatility")
aMyArray(j, k + 8) = Json("records")("data")(i)("PE")("totalTradedVolume")
aMyArray(j, k + 9) = Format(Json("records")("data")(i)("PE")
("changeinOpenInterest"), "#.00")
aMyArray(j, k + 10) = Json("records")("data")(i)("PE")("openInterest")
ElseIf emptyInfo = 1 Then
aMyArray(j, k + 5) = Json("records")("data")(i)("strikePrice")
aMyArray(j, k + 6) = Json("records")("data")(i)("PE")("lastPrice")
aMyArray(j, k + 7) = Json("records")("data")(i)("PE")("impliedVolatility")
aMyArray(j, k + 8) = Json("records")("data")(i)("PE")("totalTradedVolume")
aMyArray(j, k + 9) = Format(Json("records")("data")(i)("PE")
("changeinOpenInterest"), "#.00")
aMyArray(j, k + 10) = Json("records")("data")(i)("PE")("openInterest")
Else
aMyArray(j, k + 6) = ""
aMyArray(j, k + 7) = ""
aMyArray(j, k + 8) = ""
aMyArray(j, k + 9) = ""
aMyArray(j, k + 10) = ""
End If
End If

End If

Next i

Range("A4:K" & j + 3) = aMyArray


Columns("A:K").AutoFit
Columns("A:K").VerticalAlignment = xlCenter
Columns("A:K").HorizontalAlignment = xlCenter
Cells(1, 1).Select

[Link] = True

End Sub
________________________________________

Module3
_________________________________________________

Option Explicit
Sub specific_browser(ByVal sURL As String)

Dim sPath As Variant, dummy

sPath = "C:\Program Files (x86)\Mozilla Firefox\[Link]"


If Dir(sPath) = "" Then
sPath = "C:\Program Files\Mozilla Firefox\[Link]"
End If
If Dir(sPath) = "" Then
sPath = "C:\Program Files\Google\Chrome\Application\[Link]"
End If

dummy = Shell(sPath & " " & sURL, vbMinimizedFocus)

End Sub

You might also like