Do You want to perform analysis on open source yahoo financial data which can be accessed by JSON ? So how do you achieve this?

So let’s get started.

1. We can achieve this by either using Iron Phyton Script .
2. Or we can achieve this by using CIS(Cisco Composite Studio) a Data Virtualization service.

In this post, we will load the JSON data by using Iron Phyton Script.

Step 1:

Create a document Property called “Ticker Symbol” & enter any ticker symbol value.

Step 2:

import clr
clr.AddReference(‘System.Data’)
clr.AddReference(‘System.Web.Extensions’)
import System
from System import DateTime
from System.Data import DataSet, DataTable
from System.IO import StreamReader, StreamWriter, MemoryStream, SeekOrigin
from System.Net import HttpWebRequest
from System.Web.Script.Serialization import JavaScriptSerializer
from Spotfire.Dxp.Data import DataType, DataTableSaveSettings
from Spotfire.Dxp.Data.Import import TextFileDataSource, TextDataReaderSettings

# get stock quotes data in JSON format from Yahoo Finance API
tickerSymbol = Document.Properties[“TickerSymbol”]
startDate = DateTime.Today.AddMonths(-3).ToString(“yyyy-MM-dd”)
endDate = DateTime.Today.ToString(“yyyy-MM-dd”)
uri = “http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.historicaldata where symbol = %22” + tickerSymbol + “%22 and startDate = %22” + startDate + “%22 and endDate = %22” + endDate + “%22&format=json&diagnostics=true&env=store://datatables.org/alltableswithkeys”
webRequest = HttpWebRequest.Create(uri)
response = webRequest.GetResponse()
streamReader = StreamReader(response.GetResponseStream())
jsonData = streamReader.ReadToEnd()
js = JavaScriptSerializer()
dataDict = js.Deserialize(jsonData,object)

# build a string representing the data in comma-delimited text format
textData = “Symbol,Date,Close\r\n”
for quote in dataDict[“query”][“results”][“quote”]:
textData += tickerSymbol + “,” + quote[“Date”] + “,” + quote[“Close”] + “\r\n”

# make a stream from the string
stream = MemoryStream()
writer = StreamWriter(stream)
writer.Write(textData)
writer.Flush()
stream.Seek(0, SeekOrigin.Begin)

# set up the text data reader
readerSettings = TextDataReaderSettings()
readerSettings.Separator = “,”
readerSettings.AddColumnNameRow(0)
readerSettings.SetDataType(0, DataType.String)
readerSettings.SetDataType(1, DataType.Date)
readerSettings.SetDataType(2, DataType.Currency)

# create a data source to read in the stream
textDataSource = TextFileDataSource(stream, readerSettings)

# add the data into a Data Table in Spotfire
if Document.Data.Tables.Contains(“Stock Data”):
Document.Data.Tables[“Stock Data”].ReplaceData(textDataSource)
else:
newTable = Document.Data.Tables.Add(“Stock Data”, textDataSource)
tableSettings = DataTableSaveSettings (newTable, False, False)
Document.Data.SaveSettings.DataTableSettings.Add(tableSettings)

There is where you will write your script.

Note: The above code has  been tested  6.5,7.0 Version Spotfire.