' ******************************************************************************
' **
' ** 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 CSV
''' </summary>
''' <remarks></remarks>
Public Class CSV
Private ReadOnly mHelper As New MyHelper
Private ReadOnly mFinanceHelper As New FinanceHelper
''' <summary>
''' Converts a list of basic quote data to a CSV formatted text
''' </summary>
''' <param name="quotes">The list of quote values</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted data string in CSV format</returns>
''' <remarks></remarks>
Public Function FromQuotesBaseData(ByVal quotes As IEnumerable(Of QuoteBaseData), ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As String
Dim csv As New Text.StringBuilder
If quotes IsNot Nothing Then
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
For Each qbd As QuoteBaseData In quotes
csv.Append("""")
csv.Append(qbd.ID)
csv.Append("""")
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.LastTradePriceOnly, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.Change, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.Open, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.DaysHigh, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.DaysLow, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.Volume, ci))
csv.Append(delimiter)
csv.Append(qbd.LastTradeDate.ToShortDateString)
csv.Append(delimiter)
csv.AppendLine(qbd.LastTradeTime.ToShortTimeString)
Next
End If
Return csv.ToString
End Function
''' <summary>
''' Tries to read a list of basic quote data from a CSV formatted text
''' </summary>
''' <param name="csvText">The CSV formatted text</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted quote values or Nothing</returns>
''' <remarks></remarks>
Public Function ToQuotesBaseData(ByVal csvText As String, ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As QuoteBaseData()
If csvText <> String.Empty Then
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
Dim table()() As String = mHelper.CsvTextToStringTable(csvText, delimiter)
Dim lst As New List(Of QuoteBaseData)
If table.Length > 1 Then
For i As Integer = 0 To table.Length - 1
If table(i).Length = 9 Then
Dim qd As New QuoteBaseData
If Double.TryParse(table(i)(1), Globalization.NumberStyles.Currency, ci, qd.LastTradePriceOnly) AndAlso _
Double.TryParse(table(i)(2), Globalization.NumberStyles.Currency, ci, qd.Change) AndAlso _
Double.TryParse(table(i)(3), Globalization.NumberStyles.Currency, ci, qd.Open) AndAlso _
Double.TryParse(table(i)(4), Globalization.NumberStyles.Currency, ci, qd.DaysHigh) AndAlso _
Double.TryParse(table(i)(5), Globalization.NumberStyles.Currency, ci, qd.DaysLow) AndAlso _
Long.TryParse(table(i)(6), Globalization.NumberStyles.Integer, ci, qd.Volume) AndAlso _
Date.TryParse(table(i)(7), ci, Globalization.DateTimeStyles.AdjustToUniversal, qd.LastTradeDate) AndAlso _
Date.TryParse(table(i)(8), ci, Globalization.DateTimeStyles.AdjustToUniversal, qd.LastTradeTime) Then
qd.SetID(table(i)(0))
End If
lst.Add(qd)
End If
Next
End If
Return lst.ToArray
Else
Return Nothing
End If
End Function
''' <summary>
''' Converts a list of quote data to a CSV formatted text
''' </summary>
''' <param name="quotes">The list of quote values</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted data string in CSV format</returns>
''' <remarks></remarks>
Public Function FromQuotesData(ByVal quotes As IEnumerable(Of QuoteData), ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As String
Return Me.FromQuotesData(quotes, delimiter, Nothing, culture)
End Function
''' <summary>
''' Converts a list of quote data to a CSV formatted text
''' </summary>
''' <param name="quotes">The list of quote values</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="properties">The used properties of the items</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted data string in CSV format</returns>
''' <remarks></remarks>
Public Function FromQuotesData(ByVal quotes As IEnumerable(Of QuoteData), ByVal delimiter As Char, ByVal properties As IEnumerable(Of QuoteProperty), Optional ByVal culture As Globalization.CultureInfo = Nothing) As String
If quotes IsNot Nothing Then
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
Dim prpts() As QuoteProperty = mFinanceHelper.CheckPropertiesOfQuotesData(quotes, properties)
Dim sb As New Text.StringBuilder
For Each qp As QuoteProperty In prpts
sb.Append(qp.ToString)
sb.Append(delimiter)
Next
sb.Remove(sb.Length - 1, 1)
sb.AppendLine()
For Each q As QuoteData In quotes
If q IsNot Nothing Then
Dim sbQ As New Text.StringBuilder
For Each qp As QuoteProperty In prpts
Dim o As Object = mHelper.ObjectToString(q(qp), ci)
If TypeOf o Is String Then
If o.ToString = String.Empty Then
sbQ.Append("""N/A""")
Else
sbQ.Append("""")
sbQ.Append(q(qp).ToString.Replace("""", """"""))
sbQ.Append("""")
End If
Else
sbQ.Append(mHelper.ObjectToString(q(qp), ci))
End If
sbQ.Append(delimiter)
Next
If sbQ.Length > 0 Then sbQ.Remove(sbQ.Length - 1, 1)
sb.AppendLine(sbQ.ToString)
End If
Next
Return sb.ToString
Else
Return String.Empty
End If
End Function
''' <summary>
''' Tries to read a list of quote data from a CSV formatted text
''' </summary>
''' <param name="csvText">The CSV formatted text</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted quote values or Nothing</returns>
''' <remarks></remarks>
Public Function ToQuotesData(ByVal csvText As String, ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As QuoteData()
Dim properties As New List(Of QuoteProperty)
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
If csvText <> String.Empty Then
Dim rows() As String = csvText.Split(Chr(10))
Dim headerParts() As String = mHelper.CsvRowToStringArray(rows(0), delimiter)
For Each part As String In headerParts
For qp As QuoteProperty = 0 To QuoteProperty.YearRange
If qp.ToString = part.Trim Then
properties.Add(qp)
Exit For
End If
Next
Next
If properties.Count <> headerParts.Length Then Return Nothing
End If
Return Me.ToQuotesData(csvText, delimiter, properties.ToArray, ci, True)
End Function
Friend Function ToQuotesData(ByVal csvText As String, ByVal delimiter As Char, ByVal properties() As QuoteProperty, ByVal culture As Globalization.CultureInfo, Optional ByVal hasHeader As Boolean = False) As QuoteData()
Dim quotes As New List(Of QuoteData)
If csvText <> String.Empty And culture IsNot Nothing And (properties IsNot Nothing AndAlso properties.Length > 0) Then
If properties.Length > 0 Then
Dim table As String()() = mHelper.CsvTextToStringTable(csvText, delimiter)
Dim start As Integer = 0
If hasHeader Then start = 1
If table.Length > 0 Then
If Not table(0).Length = properties.Length Then
table = mHelper.CsvTextToStringTable(csvText, CChar(IIf(delimiter = ";"c, ","c, ";"c)))
End If
If table.Length > 0 AndAlso (table(0).Length = properties.Length And table.Length - 1 >= start) Then
For i As Integer = start To table.Length - 1
Dim qd As QuoteData = Me.CsvArrayToQuoteData(table(i), properties, culture)
If qd IsNot Nothing Then quotes.Add(qd)
Next
End If
End If
End If
End If
Return quotes.ToArray
End Function
''' <summary>
''' Converts a list of quote options to a CSV formatted text
''' </summary>
''' <param name="quoteOptions">The list of quote option values</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted data string in CSV format</returns>
''' <remarks></remarks>
Public Function FromQuoteOptions(ByVal quoteOptions As IEnumerable(Of QuoteOptionData), ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As String
Dim csv As New Text.StringBuilder
If quoteOptions IsNot Nothing Then
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
For Each qbd As QuoteOptionData In quoteOptions
csv.Append("""")
csv.Append(qbd.Symbol)
csv.Append("""")
csv.Append("""")
csv.Append(IIf(qbd.Type = QuoteOptionType.Call, "C", "P").ToString)
csv.Append("""")
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.LastPrice, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.StrikePrice, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(Math.Abs(qbd.Change), ci))
csv.Append(delimiter)
csv.Append("""")
csv.Append(IIf(qbd.Change >= 0, "Up", "Down").ToString())
csv.Append("""")
csv.Append(mHelper.ObjectToString(qbd.Bid, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.Ask, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.Volume, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(qbd.OpenInterest, ci))
Next
End If
Return csv.ToString
End Function
''' <summary>
''' Tries to read a list of quote options from a CSV formatted text
''' </summary>
''' <param name="csvText">The CSV formatted text</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted quote values or Nothing</returns>
''' <remarks></remarks>
Public Function ToQuoteOptions(ByVal csvText As String, ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As QuoteOptionData()
Dim lst As New List(Of QuoteOptionData)
If csvText <> String.Empty Then
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
Dim table()() As String = mHelper.CsvTextToStringTable(csvText, delimiter)
If table.Length > 1 Then
For i As Integer = 0 To table.Length - 1
If table(i).Length = 10 Then
Dim qd As New QuoteOptionData
qd.Symbol = table(i)(0)
qd.Type = DirectCast(IIf(table(i)(1).ToLower = "p", QuoteOptionType.Put, QuoteOptionType.Call), QuoteOptionType)
If Double.TryParse(table(i)(2), Globalization.NumberStyles.Currency, ci, qd.LastPrice) AndAlso _
Double.TryParse(table(i)(3), Globalization.NumberStyles.Currency, ci, qd.StrikePrice) AndAlso _
Double.TryParse(table(i)(4), Globalization.NumberStyles.Currency, ci, qd.Change) AndAlso _
Double.TryParse(table(i)(6), Globalization.NumberStyles.Currency, ci, qd.Bid) AndAlso _
Double.TryParse(table(i)(7), Globalization.NumberStyles.Currency, ci, qd.Ask) AndAlso _
Integer.TryParse(table(i)(8), Globalization.NumberStyles.Integer, ci, qd.Volume) AndAlso _
Integer.TryParse(table(i)(9), Globalization.NumberStyles.Integer, ci, qd.OpenInterest) Then
qd.Change *= Convert.ToInt32(IIf(table(i)(5).ToLower = "down", -1, 1))
lst.Add(qd)
End If
End If
Next
End If
End If
Return lst.ToArray
End Function
''' <summary>
''' Converts a list of historic quote periods to a CSV formatted text
''' </summary>
''' <param name="quotes">The list of historic quote periods</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted data string in CSV format</returns>
''' <remarks></remarks>
Public Function FromHistQuotesData(ByVal quotes As IEnumerable(Of HistQuoteData), ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As String
Dim csv As New Text.StringBuilder
If quotes IsNot Nothing Then
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
csv.AppendLine(HistQuotesCSVHeadline(delimiter))
For Each hq As HistQuoteData In quotes
csv.Append(mHelper.ObjectToString(hq.TradingDate, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(hq.Open, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(hq.High, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(hq.Low, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(hq.Close, ci))
csv.Append(delimiter)
csv.Append(mHelper.ObjectToString(hq.Volume, ci))
csv.Append(delimiter)
csv.AppendLine(mHelper.ObjectToString(hq.CloseAdjusted, ci))
Next
End If
Return csv.ToString
End Function
''' <summary>
''' Tries to read a list of historic quote periods from a CSV formatted text
''' </summary>
''' <param name="csvText">The CSV formatted text</param>
''' <param name="delimiter">The delimiter of the CSV text</param>
''' <param name="culture">The used culture for formating dates and numbers. If parameter value is null/Nothing, default Culture will be used.</param>
''' <returns>The converted historic quote periods or Nothing</returns>
''' <remarks></remarks>
Public Function ToHistQuotesData(ByVal csvText As String, ByVal delimiter As Char, Optional ByVal culture As Globalization.CultureInfo = Nothing) As HistQuoteData()
Dim lst As New List(Of HistQuoteData)
If csvText <> String.Empty Then
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
Dim table()() As String = mHelper.CsvTextToStringTable(csvText, delimiter)
If table.Length > 1 Then
For i As Integer = 0 To table.Length - 1
If table(i).Length = 7 Then
Dim qd As New HistQuoteData
If Date.TryParse(table(i)(0), culture, Globalization.DateTimeStyles.None, qd.TradingDate) AndAlso _
Double.TryParse(table(i)(1), Globalization.NumberStyles.Currency, culture, qd.Open) AndAlso _
Double.TryParse(table(i)(2), Globalization.NumberStyles.Currency, culture, qd.High) AndAlso _
Double.TryParse(table(i)(3), Globalization.NumberStyles.Currency, culture, qd.Low) AndAlso _
Double.TryParse(table(i)(4), Globalization.NumberStyles.Currency, culture, qd.Close) AndAlso _
Long.TryParse(table(i)(5), Globalization.NumberStyles.Integer, culture, qd.Volume) AndAlso _
Double.TryParse(table(i)(6), Globalization.NumberStyles.Currency, culture, qd.CloseAdjusted) Then
lst.Add(qd)
End If
End If
Next
End If
End If
Return lst.ToArray
End Function
Public Function ToMarketQuotesData(ByVal csv As String, Optional ByVal culture As Globalization.CultureInfo = Nothing) As MarketQuoteData()
Dim ci As Globalization.CultureInfo = Globalization.CultureInfo.CurrentCulture
If culture IsNot Nothing Then ci = culture
Dim delimiter As Char = ","c
Dim table()() As String = mHelper.CsvTextToStringTable(csv, delimiter)
Dim lst As New List(Of MarketQuoteData)
If table.Length > 1 Then
For i As Integer = 1 To table.Length - 1
If table(i).Length = 10 Then
Dim quote As New MarketQuoteData
quote.Name = table(i)(0)
Double.TryParse(table(i)(1), Globalization.NumberStyles.Any, ci, quote.OneDayPriceChangePercent)
Dim mktcap As String = table(i)(2)
If mktcap <> "NA" And mktcap <> String.Empty And mktcap.Length > 1 Then
Dim value As Double = 0
Double.TryParse(mktcap.Substring(0, mktcap.Length - 1), Globalization.NumberStyles.Any, ci, value)
quote.MarketCapitalizationInMillion = value * mFinanceHelper.GetStringMillionFactor(mktcap)
End If
Double.TryParse(table(i)(3), Globalization.NumberStyles.Any, ci, quote.PriceEarningsRatio)
Double.TryParse(table(i)(4), Globalization.NumberStyles.Any, ci, quote.ReturnOnEquityPercent)
Double.TryParse(table(i)(5), Globalization.NumberStyles.Any, ci, quote.DividendYieldPercent)
Double.TryParse(table(i)(6), Globalization.NumberStyles.Any, ci, quote.LongTermDeptToEquity)
Double.TryParse(table(i)(7), Globalization.NumberStyles.Any, ci, quote.PriceToBookValue)
Double.TryParse(table(i)(8), Globalization.NumberStyles.Any, ci, quote.NetProfitMarginPercent)
Double.TryParse(table(i)(9), Globalization.NumberStyles.Any, ci, quote.PriceToFreeCashFlow)
lst.Add(quote)
End If
Next
End If
Return lst.ToArray
End Function
Private Function HistQuotesCSVHeadline(ByVal delimiter As Char) As String
Return String.Format("{1}{0}{2}{0}{3}{0}{4}{0}{5}{0}{6}{0}Adj Close", delimiter, FinanceHelper.NameHistQuoteDate, FinanceHelper.NameHistQuoteOpen, FinanceHelper.NameHistQuoteHigh, FinanceHelper.NameHistQuoteLow, FinanceHelper.NameHistQuoteClose, FinanceHelper.NameHistQuoteVolume)
End Function
Private Function CsvArrayToQuoteData(ByVal rowItems As String(), ByVal properties() As QuoteProperty, ByVal culture As Globalization.CultureInfo) As QuoteData
If rowItems.Length > 0 Then
Dim quote As New QuoteData
If rowItems.Length = properties.Length Then
For i As Integer = 0 To properties.Length - 1
quote(properties(i)) = mHelper.StringToObject(rowItems(i), culture)
Next
End If
Return quote
Else
Return Nothing
End If
End Function
End Class
End Namespace