' ******************************************************************************
' **
' ** Yahoo Finance Managed
' ** Written by Marius Häusler 2010
' ** It would be pleasant, if you contact me when you are using this code.
' ** Contact: YahooFinanceManaged@gmail.com
' ** Project Home: http://code.google.com/p/yahoo-finance-managed/
' **
' ******************************************************************************
' **
' ** Copyright 2010 Marius Häusler
' **
' ** Licensed under the Apache License, Version 2.0 (the "License");
' ** you may not use this file except in compliance with the License.
' ** You may obtain a copy of the License at
' **
' ** http://www.apache.org/licenses/LICENSE-2.0
' **
' ** Unless required by applicable law or agreed to in writing, software
' ** distributed under the License is distributed on an "AS IS" BASIS,
' ** WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
' ** See the License for the specific language governing permissions and
' ** limitations under the License.
' **
' ******************************************************************************
Namespace Finance.ImportExport
''' <summary>
''' Class for parsing managed data to and from DataTables
''' </summary>
''' <remarks></remarks>
Public Class DataTable
Private ReadOnly mFinanceHelper As New FinanceHelper
''' <summary>
''' Converts a list of quote values to a system.data.DataTable
''' </summary>
''' <param name="quotes">The list of quote values</param>
''' <returns>The converted System.Data.DataTable containing the quote informations</returns>
''' <remarks></remarks>
Public Function FromQuotesBaseData(ByVal quotes As IEnumerable(Of QuoteBaseData)) As System.Data.DataTable
If quotes IsNot Nothing Then
Dim dt As New System.Data.DataTable
dt.Columns.Add(FinanceHelper.NameQuoteBaseID, GetType(String))
dt.Columns.Add(FinanceHelper.NameQuoteBaseLastTradePriceOnly, GetType(Double))
dt.Columns.Add(FinanceHelper.NameQuoteBaseChange, GetType(Double))
dt.Columns.Add(FinanceHelper.NameQuoteBaseOpen, GetType(Double))
dt.Columns.Add(FinanceHelper.NameQuoteBaseDaysHigh, GetType(Double))
dt.Columns.Add(FinanceHelper.NameQuoteBaseDaysLow, GetType(Double))
dt.Columns.Add(FinanceHelper.NameQuoteBaseVolume, GetType(Integer))
dt.Columns.Add(FinanceHelper.NameQuoteBaseLastTradeDate, GetType(Date))
dt.Columns.Add(FinanceHelper.NameQuoteBaseLastTradeTime, GetType(Date))
For Each q As QuoteBaseData In quotes
Dim row As System.Data.DataRow = dt.NewRow
row(FinanceHelper.NameQuoteBaseID) = q.ID
row(FinanceHelper.NameQuoteBaseLastTradePriceOnly) = q.LastTradePriceOnly
row(FinanceHelper.NameQuoteBaseChange) = q.Change
row(FinanceHelper.NameQuoteBaseOpen) = q.Open
row(FinanceHelper.NameQuoteBaseDaysHigh) = q.DaysHigh
row(FinanceHelper.NameQuoteBaseDaysLow) = q.DaysLow
row(FinanceHelper.NameQuoteBaseVolume) = q.Volume
row(FinanceHelper.NameQuoteBaseLastTradeDate) = q.LastTradeDate
row(FinanceHelper.NameQuoteBaseLastTradeTime) = q.LastTradeTime
dt.Rows.Add(row)
Next
Return dt
Else
Return Nothing
End If
End Function
''' <summary>
''' Tries to read a list of quote values from a System.Data.DataTable
''' </summary>
''' <param name="table">The System.Data.DataTable with the quote values</param>
''' <returns>The converted quote values or Nothing</returns>
''' <remarks></remarks>
Public Function ToQuotesBaseData(ByVal table As System.Data.DataTable) As QuoteBaseData()
Dim lst As New List(Of QuoteBaseData)
If (table.Columns.Contains(FinanceHelper.NameQuoteBaseID) AndAlso table.Columns(FinanceHelper.NameQuoteBaseID).DataType Is GetType(String)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseLastTradePriceOnly) AndAlso table.Columns(FinanceHelper.NameQuoteBaseLastTradePriceOnly).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseChange) AndAlso table.Columns(FinanceHelper.NameQuoteBaseChange).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseOpen) AndAlso table.Columns(FinanceHelper.NameQuoteBaseOpen).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseDaysHigh) AndAlso table.Columns(FinanceHelper.NameQuoteBaseDaysHigh).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseDaysLow) AndAlso table.Columns(FinanceHelper.NameQuoteBaseDaysLow).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseVolume) AndAlso table.Columns(FinanceHelper.NameQuoteBaseVolume).DataType Is GetType(Integer)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseLastTradeDate) AndAlso table.Columns(FinanceHelper.NameQuoteBaseLastTradeDate).DataType Is GetType(Date)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameQuoteBaseLastTradeTime) AndAlso table.Columns(FinanceHelper.NameQuoteBaseLastTradeTime).DataType Is GetType(Date)) Then
For Each row As System.Data.DataRow In table.Rows
Dim qd As New QuoteBaseData
qd.SetID(row(FinanceHelper.NameQuoteBaseID).ToString)
qd.LastTradePriceOnly = Convert.ToDouble(row(FinanceHelper.NameQuoteBaseLastTradePriceOnly))
qd.Change = Convert.ToDouble(row(FinanceHelper.NameQuoteBaseChange))
qd.Open = Convert.ToDouble(row(FinanceHelper.NameQuoteBaseOpen))
qd.DaysHigh = Convert.ToDouble(row(FinanceHelper.NameQuoteBaseDaysHigh))
qd.DaysLow = Convert.ToDouble(row(FinanceHelper.NameQuoteBaseDaysLow))
qd.Volume = Convert.ToInt32(row(FinanceHelper.NameQuoteBaseVolume))
qd.LastTradeDate = Convert.ToDateTime(row(FinanceHelper.NameQuoteBaseLastTradeDate))
qd.LastTradeTime = Convert.ToDateTime(row(FinanceHelper.NameQuoteBaseLastTradeTime))
lst.Add(qd)
Next
End If
Return lst.ToArray
End Function
''' <summary>
''' Converts a list of quote values to a System.Data.DataTable
''' </summary>
''' <param name="quotes">The list of quote values</param>
''' <param name="properties">The used properties of the items, representing the column headers</param>
''' <returns>The converted system.data.DataTable containing the quote informations</returns>
''' <remarks></remarks>
Public Function FromQuotesData(ByVal quotes As IEnumerable(Of QuoteData), Optional ByVal properties As IEnumerable(Of QuoteProperty) = Nothing) As System.Data.DataTable
If quotes IsNot Nothing Then
Dim dt As New System.Data.DataTable
Dim lst As New List(Of QuoteData)(quotes)
Dim prps() As QuoteProperty = mFinanceHelper.CheckPropertiesOfQuotesData(lst, properties)
For Each p As QuoteProperty In prps
Dim colT As Type
If lst(0)(p) IsNot Nothing Then
colT = lst(0)(p).GetType
dt.Columns.Add(p.ToString, colT)
Else
colT = GetType(Object)
dt.Columns.Add(p.ToString, colT)
End If
Next
For Each q As QuoteData In lst
Dim row As System.Data.DataRow = dt.NewRow
For Each p As QuoteProperty In prps
If q(p) IsNot Nothing Then row(p.ToString) = q(p)
Next
dt.Rows.Add(row)
Next
Return dt
Else
Return Nothing
End If
End Function
''' <summary>
''' Tries to read a list of quote values from a System.Data.DataTable
''' </summary>
''' <param name="table">The system.data.DataTable with the quote values</param>
''' <returns>The converted quote values or Nothing</returns>
''' <remarks></remarks>
Public Function ToQuotesData(ByVal table As System.Data.DataTable) As QuoteData()
If table IsNot Nothing Then
Dim lst As New List(Of QuoteData)
For Each row As System.Data.DataRow In table.Rows
Dim quote As New QuoteData
For Each col As System.Data.DataColumn In table.Columns
For qp As QuoteProperty = 0 To QuoteProperty.YearRange
If col.ColumnName = qp.ToString Then
quote(qp) = row(col.ColumnName)
Exit For
End If
Next
Next
lst.Add(quote)
Next
Return lst.ToArray
Else
Return Nothing
End If
End Function
''' <summary>
''' Converts a list of quote values to a System.Data.DataTable
''' </summary>
''' <param name="quotes">The list of quote values</param>
''' <returns>The converted system.data.DataTable containing the quote informations</returns>
''' <remarks></remarks>
Public Function FromQuoteOptions(ByVal quotes As IEnumerable(Of QuoteOptionData)) As System.Data.DataTable
If quotes IsNot Nothing Then
Dim dt As New System.Data.DataTable
dt.Columns.Add(FinanceHelper.NameOptionSymbol, GetType(String))
dt.Columns.Add(FinanceHelper.NameOptionType, GetType(QuoteOptionType))
dt.Columns.Add(FinanceHelper.NameOptionLastPrice, GetType(Double))
dt.Columns.Add(FinanceHelper.NameOptionStrikePrice, GetType(Double))
dt.Columns.Add(FinanceHelper.NameOptionChange, GetType(Double))
dt.Columns.Add(FinanceHelper.NameOptionBid, GetType(Double))
dt.Columns.Add(FinanceHelper.NameOptionAsk, GetType(Double))
dt.Columns.Add(FinanceHelper.NameOptionVolume, GetType(Integer))
dt.Columns.Add(FinanceHelper.NameOptionOpenInterest, GetType(Integer))
For Each q As QuoteOptionData In quotes
Dim row As System.Data.DataRow = dt.NewRow
row(FinanceHelper.NameOptionSymbol) = q.Symbol
row(FinanceHelper.NameOptionType) = q.Type
row(FinanceHelper.NameOptionLastPrice) = q.LastPrice
row(FinanceHelper.NameOptionStrikePrice) = q.StrikePrice
row(FinanceHelper.NameOptionChange) = q.Change
row(FinanceHelper.NameOptionBid) = q.Bid
row(FinanceHelper.NameOptionAsk) = q.Ask
row(FinanceHelper.NameOptionVolume) = q.Volume
row(FinanceHelper.NameOptionOpenInterest) = q.OpenInterest
dt.Rows.Add(row)
Next
Return dt
Else
Return Nothing
End If
End Function
''' <summary>
''' Tries to read a list of quote values from a System.Data.DataTable
''' </summary>
''' <param name="table">The system.data.DataTable with the quote values</param>
''' <returns>The converted quote values or Nothing</returns>
''' <remarks></remarks>
Public Function ToQuoteOptions(ByVal table As System.Data.DataTable) As QuoteOptionData()
Dim lst As New List(Of QuoteOptionData)
If (table.Columns.Contains(FinanceHelper.NameOptionSymbol) AndAlso table.Columns(FinanceHelper.NameOptionSymbol).DataType Is GetType(String)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionType) AndAlso table.Columns(FinanceHelper.NameOptionType).DataType Is GetType(QuoteOptionType)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionLastPrice) AndAlso table.Columns(FinanceHelper.NameOptionLastPrice).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionStrikePrice) AndAlso table.Columns(FinanceHelper.NameOptionStrikePrice).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionChange) AndAlso table.Columns(FinanceHelper.NameOptionChange).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionBid) AndAlso table.Columns(FinanceHelper.NameOptionBid).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionAsk) AndAlso table.Columns(FinanceHelper.NameOptionAsk).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionVolume) AndAlso table.Columns(FinanceHelper.NameOptionVolume).DataType Is GetType(Integer)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameOptionOpenInterest) AndAlso table.Columns(FinanceHelper.NameOptionOpenInterest).DataType Is GetType(Integer)) Then
For Each row As System.Data.DataRow In table.Rows
Dim qd As New QuoteOptionData(row(FinanceHelper.NameOptionSymbol).ToString, _
DirectCast(row(FinanceHelper.NameOptionType), QuoteOptionType), _
Convert.ToDouble(row(FinanceHelper.NameOptionLastPrice)), _
Convert.ToDouble(row(FinanceHelper.NameOptionStrikePrice)), _
Convert.ToDouble(row(FinanceHelper.NameOptionChange)), _
Convert.ToDouble(row(FinanceHelper.NameOptionBid)), _
Convert.ToDouble(row(FinanceHelper.NameOptionAsk)), _
Convert.ToInt32(row(FinanceHelper.NameOptionVolume)), _
Convert.ToInt32(row(FinanceHelper.NameOptionOpenInterest)))
lst.Add(qd)
Next
End If
Return lst.ToArray
End Function
''' <summary>
''' Converts a list of historic quote periods to a System.Data.DataTable
''' </summary>
''' <param name="quotes">The list of historic quote periods</param>
''' <returns>The converted system.data.DataTable containing the historic quote informations</returns>
''' <remarks></remarks>
Public Function FromHistQuotesData(ByVal quotes As IEnumerable(Of HistQuoteData)) As System.Data.DataTable
Dim dt As New System.Data.DataTable
dt.Columns.Add(FinanceHelper.NameHistQuoteDate, GetType(Date))
dt.Columns.Add(FinanceHelper.NameHistQuoteOpen, GetType(Double))
dt.Columns.Add(FinanceHelper.NameHistQuoteHigh, GetType(Double))
dt.Columns.Add(FinanceHelper.NameHistQuoteLow, GetType(Double))
dt.Columns.Add(FinanceHelper.NameHistQuoteClose, GetType(Double))
dt.Columns.Add(FinanceHelper.NameHistQuoteVolume, GetType(Long))
dt.Columns.Add(FinanceHelper.NameHistQuoteAdjClose, GetType(Double))
If quotes IsNot Nothing Then
For Each quote As HistQuoteData In quotes
Dim row As System.Data.DataRow = dt.NewRow
row(FinanceHelper.NameHistQuoteDate) = quote.TradingDate
row(FinanceHelper.NameHistQuoteOpen) = quote.Open
row(FinanceHelper.NameHistQuoteHigh) = quote.High
row(FinanceHelper.NameHistQuoteLow) = quote.Low
row(FinanceHelper.NameHistQuoteClose) = quote.Close
row(FinanceHelper.NameHistQuoteVolume) = quote.Volume
row(FinanceHelper.NameHistQuoteAdjClose) = quote.CloseAdjusted
dt.Rows.Add(row)
Next
End If
Return dt
End Function
''' <summary>
''' Tries to read a list of historic quote periods from a System.Data.DataTable
''' </summary>
''' <param name="table">The system.data.DataTable with the historic quote periods</param>
''' <returns>The converted quote periods or Nothing</returns>
''' <remarks></remarks>
Public Function ToHistQuotesData(ByVal table As System.Data.DataTable) As HistQuoteData()
Dim lst As New List(Of HistQuoteData)
If (table.Columns.Contains(FinanceHelper.NameHistQuoteDate) AndAlso table.Columns(FinanceHelper.NameOptionSymbol).DataType Is GetType(Date)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameHistQuoteOpen) AndAlso table.Columns(FinanceHelper.NameOptionType).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameHistQuoteHigh) AndAlso table.Columns(FinanceHelper.NameOptionLastPrice).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameHistQuoteLow) AndAlso table.Columns(FinanceHelper.NameOptionStrikePrice).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameHistQuoteClose) AndAlso table.Columns(FinanceHelper.NameOptionChange).DataType Is GetType(Double)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameHistQuoteVolume) AndAlso table.Columns(FinanceHelper.NameOptionBid).DataType Is GetType(Long)) AndAlso _
(table.Columns.Contains(FinanceHelper.NameHistQuoteAdjClose) AndAlso table.Columns(FinanceHelper.NameOptionAsk).DataType Is GetType(Double)) Then
For Each row As System.Data.DataRow In table.Rows
Dim qd As New HistQuoteData
qd.TradingDate = Convert.ToDateTime(row(FinanceHelper.NameHistQuoteDate))
qd.Open = Convert.ToDouble(row(FinanceHelper.NameHistQuoteOpen))
qd.High = Convert.ToDouble(row(FinanceHelper.NameHistQuoteHigh))
qd.Low = Convert.ToDouble(row(FinanceHelper.NameHistQuoteLow))
qd.Close = Convert.ToDouble(row(FinanceHelper.NameHistQuoteClose))
qd.Volume = Convert.ToInt64(row(FinanceHelper.NameHistQuoteVolume))
qd.CloseAdjusted = Convert.ToDouble(row(FinanceHelper.NameHistQuoteAdjClose))
lst.Add(qd)
Next
End If
Return lst.ToArray
End Function
End Class
End Namespace
Submit an article or tip