Any help will be appreciated.
After remove the first line I could not figure out, should I use select case, maybe InStr with vbBinaryCompare ...
This is the string I need to parse.
From 9:30AM until 4:00PM - Only show time 11:30:54 AM,
symbol,latestTime,open,high,low,close,
PSEC,11:30:54 AM,6.63,6.64,6.61,6.61
' This one works fine
After 4:00 PM - show March 15, 2019,
symbol,latestTime,open,high,low,close,
PSEC,"March 15,2019",6.63,6.64,6.61,6.61,
Code:
Function ParseStockQuote&(Main$, tStockDetails As StockDetails)
tMain$ = Main$
On Error Resume Next 'GoTo ParseError
With tStockDetails
DoEvents
'******** Remove first line ************
Call StripFirstLine$(tMain$) ' to remove first line
Debug.Print tMain$
Dim CSV$(1 To 6)
For n& = 1 To 6
CSV$(n&) = SubtractString$(tMain$, ",")
Next
.Ticker = CSV$(1) 'PSEC
.LatestTime = CSV$(2) 'Time
If LatestTime = "N/A" Then 'no date no good
ParseStockQuote& = 2 'not a valid date resubmit ?
Exit Function
End If
.Open = Val(CSV$(3))
.High = Val(CSV$(4))
.Low = Val(CSV$(5))
.Close = Val(CSV$(6))
'If InStr(tTime$, "chr$(58)", vbBinaryCompare) = 0 Then 'I m trying use
Else
Exit Function
End Function
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Hi, Thanks for respond.
The parse should be CSV1 = symbol, CSV2 = time, CSV3 =Open, CSV4 =High
After 4:00PM , CSV1 is=Symbol, CSV2 is March15, CSV3 Is 2019, needs to be =open , CSV4 needs to be =high and is = low
Hi, Thanks for respond.
The parse should be CSV1 = symbol, CSV2 = time, CSV3 =Open, CSV4 =High
After 4:00PM , CSV1 is=Symbol, CSV2 is March15, CSV3 Is 2019, needs to be =open , CSV4 needs to be =high and is = low
You will need a decent CSV-Parser, which interprets "quoted text cells" within the CSV-Stream correctly.
One easy way to accomplish that is, to use a Helper-Lib which has something like that built-in.
The example below can be pasted into an empty Form (and will work with a project-reference to vbRichClient5):
Code:
Option Explicit
Implements ICSVCallback
Private CSV As cCSV, ResultRows As cArrayList
Private Sub Form_Load()
Const S As String = "symbol,latestTime,open,high,low,close," & vbLf & _
"PSEC,11:30:54 AM,6.63,6.64,6.61,6.61," & vbLf & _
"PSEC,""March 15,2019"",6.63,6.64,6.61,6.61,"
Set ResultRows = New_c.ArrayList(vbVariant) 'create a result-gathering instance
Set CSV = New_c.CSV 'create the CSV-parsing-instance
CSV.ParseBytes New_c.Crypt.VBStringToUTF8(S), Me 'and parse the UTF8-ByteArray of the CSV-Input
'the above was triggering the ICSVCallback_NewValue and filling the ResultRows-Array, ...
'now let's print what we gathered in the ICSVCallback-Routine
Dim Row As Long, Col As Long, ColArr()
For Row = 0 To ResultRows.Count - 1
ColArr = ResultRows(Row)
For Col = 0 To UBound(ColArr): Debug.Print Row, Col, ColArr(Col): Next
Next
End Sub
Private Function ICSVCallback_NewValue(ByVal RowNr As Long, ByVal ColNr As Long, B() As Byte, ByVal BValStartPos As Long, ByVal BValLen As Long) As Long
Static ColArr()
If RowNr = 0 Then ReDim Preserve ColArr(ColNr): Exit Function
ColArr(ColNr) = CSV.GetStringValue(B, BValStartPos, BValLen)
If ColNr = UBound(ColArr) Then ResultRows.Add ColArr
End Function
The above code will print out (from the 3 Rows of CSV-Input which were similar to what you've posted)
You do need to deal with the use of a quotation mark text-qualifier. You also have ragged use of an empty extra column. Is your source data really this shabby?
But your real issue would appear to be special handling you want to perform on the date-time column. That's a bit easier if you parse and convert column data to strongly-typed values instead of trying to juggle String values.
The data appears to be in either U.S. English format or possibly it is meant to be in Earth Standard ("Invariant") interchange format. Those are roughly identical, making it easy enough if the user has matching locale settings. But we can do better and parse to strong types using the Invariant Locale explicitly.
'Data rows:
Do Until Start = 0
Row = Row + 1
V = ParseLine(Start, Data)
If Row > .Rows - 1 Then .Rows = Row + 1
For Col = 0 To UBound(V)
If Col <= 5 Then
'Convert from String values to DataTypes:
V(Col) = VConvert.Convert(V(Col), DataTypes(Col))
If Col = 0 Then
.TextMatrix(Row, Col + 1) = V(Col)
ElseIf Col = 1 Then
TimeOnly = CDate(CDbl(V(Col)) - Int(CDbl(V(Col))))
If #9:30:00 AM# <= TimeOnly And TimeOnly <= #4:00:00 PM# Then
V(Col) = TimeOnly
End If
.TextMatrix(Row, Col + 1) = FormatDateTime(V(Col))
Else
.TextMatrix(Row, Col + 1) = FormatCurrency(V(Col))
End If
End If
Next
Loop
V is a Variant, VConvert is a Class included in the Project archive. The ParseLine() function is in Form1.
But this is just a guess. You haven't explained the actual problem very well so I may have wildly misinterpreted what you are trying to accomplish. And real data might be even more raggedy than the small examples you gave, so my program might fail on the real thing.
It's been a while since I've played in VB6 but my gut reaction is why not ADO? I may be a little off on the syntax but it should be pretty close. Of course you still need to declare and instantiate the connection and recordset objects.
Code:
Dim strFilePath as String
Dim fileName as String
strFilePath = "c:\Documents"
fileName = "sample.csv"
'Set the database connection
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"
'Query from the file
objRecordset.Open "SELECT * FROM " & fileName, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'Loop through the records
Do Until objRecordset.EOF
Debug.Print objRecordset.Fields.Item("symbol")
Debug.Print objRecordset.Fields.Item("latestTime")
Debug.Print objRecordset.Fields.Item("open")
Debug.Print objRecordset.Fields.Item("high")
Debug.Print objRecordset.Fields.Item("low")
Debug.Print objRecordset.Fields.Item("close")
objRecordset.MoveNext
Loop
It's been a while since I've played in VB6 but my gut reaction is why not ADO? I may be a little off on the syntax but it should be pretty close. Of course you still need to declare and instantiate the connection and recordset objects.
I'm not sure the data is in a disk file, and whether you use Jet's Text IISAM or the ODBC Desktop Text Driver (whether via ADO or DAO/RDS) you'd need to write the data to disk first. I got the idea this data was coming from an HTTP response or something.
The Text IISAM and Text Driver also risk running afoul of localization issues. For data interchange decimal point is "." and the grouping separator is "," and month names are in English, dates are mm/dd/yyyy, and so on. You can override/force some of those things via an explicit schema.ini file or custom registry settings but month names and the like are not as easy to deal with. Things like Boolean values as text "True" and "False" are issues as well.
But then you have the issue which seems to be the real question here: fiddling with the date-time values as requested. I'm not saying that one makes a lot of sense to me, but it seemed to be the only odd thing asked for but not addressed.
Hi,thank you to all for the help.
I attach some pictures so you can have an idea of the program. Meanwhile I will try see if I can implement some of the suggesting code.
The link I'm pulling the quotes is https://api.iextrading.com/1.0/stock...t,?&format=csv
I will update, thanks.
Tony
Yes , but if you notice the date of the details is from 2015, since yahoo stop the free quotes. For the details I"m try get only the Name, 52week high and week52Low.
I'm using 3 URLs to pull the data, one for charts, one for Details and one for daily update during the day.
The problem is the quote from 9:30AM until 4:00PM ( time the market is open) have only the time , after 4:00 have month and year, let's see if I can solve the issue.
Thanks Tony
Yes, the issue is with the date and time, like in your picture above
symbol,latestTime,open,high,low,latestPrice,latestVolume,change,changePercent
MO,"March 15, 2019",56.84,57.07,56.01,56.75,24537901,0.35,0.00621, From symbol to open 3 commas MO, March 15, 2019, after 4:00PM this way the 3 place is 2019
MO,"10:23:30 AM",56.84,57.07,56.01,56.75,24537901,0.35,0.00621, From symbol to open 2 commas MO, 10:23:30 AM, before 4:00PM this way the 3 place is open
The original program is in vb4 I converted to vb5, got an error when try open in vb6. I have to open your files in vb6 and try adapt I will try .
Thanks
Hi, the problem was resolved, I use a" If InStr(CSV(2), ":") <> 0 Then " This did a trick, . Is not perfect but works. What takes a minute for an expert, takes a day or more for a newbie.
I'm very grateful everyone who tried to help me, especialy to dilettante, thanks for all the help.
Code:
Function ParseStockQuote&(Main$, tStockDetails As StockDetails)
Dim tLastDate As Date
tMain$ = Main$
On Error Resume Next 'GoTo ParseError
With tStockDetails
DoEvents
'******** Remove first line ************
Call StripHTTPheader$(tMain$) ' to remove first line
tMain$ = RemoveChars$(tMain$, Chr$(34)) ' remove all ""
'convert the CSV string into an array of strings
Dim CSV$(1 To 10)
For n& = 1 To 10
CSV$(n&) = SubtractString$(tMain$, ",")
Next
.Ticker = CSV$(1) '"^OEX"
If InStr(CSV(2), ":") <> 0 Then
tLastDate = Date & " " & CSV(2)
.LastTraded = tLastDate 'Date & Time
If CSV(2) = "N/A" Then
ParseStockQuote& = 3 'exit no good no date for symbol prolly
Exit Function
End If
.Open = Val(CSV$(3)) '10085
.High = Val(CSV$(4)) '10085
.Low = Val(CSV$(5)) '9651
.LastPrice = Val(CSV$(6)) '9975
.Volume = Val(CSV$(7)) 'N/A
.PointChange = Val(CSV$(8)) '-114.69
'.PercentChange = Val(CSV$(9)) '-1.14%
.PercentChange = tMain$ * 100 '-1.14%
Else
Dim DateT As Date
DateT = CSV(2) & CSV(3) 'March 17 2019
DateT = Format(DateT, "mm/dd/yyyy")
.LastTraded = DateT '03/17/2019
'Debug.Print .LastTraded
If .LastTraded = "N/A" Then
.Open = Val(CSV$(4)) '10085
.High = Val(CSV$(5)) '10085
.Low = Val(CSV$(6)) '9651
.LastPrice = Val(CSV$(7)) '9975
.Volume = Val(CSV$(8)) 'N/A
.PointChange = Val(CSV$(9)) '-114.69
'.PercentChange = Val(CSV$(9)) '-1.14%
.PercentChange = tMain$ * 100 '-1.1
Else
.LastTraded = 0
ParseStockQuote& = 2 'not a valid date resubmit ?
End If
Exit Function
End If
End With
End Function