Page 1 of 1

Pure csv?

Posted: Mon May 21, 2012 12:00 pm
by epog10
Can someone get me started on downloading a csv file? I simply need it to finish up in a textbox or whatever for further processing.

Guess what I am looking for is the VB equivalent of Inet.

It would be nice if it did not require any extra resources other than those 'built in' within purebasic.

The following code, which obviously does not work, indicates the spirit of what I am trying to do.

Code: Select all

Global aNS.s

If OpenWindow(0, 0, 0, 1000, 600, "WebGadget", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
aNS = "http://download.finance.yahoo.com/d/quotes.csv?s=^FTSE+AV.L+HSBA.L&f=sbal1vt1k3&e=.csv"
WebGadget(1, 10, 10, 800, 500, aNS)

Repeat
  Until WaitWindowEvent() = #PB_Event_CloseWindow
EndIf

Re: Pure csv?

Posted: Mon May 21, 2012 12:18 pm
by Kiffi
Take a look at ReceiveHTTPFile()

Greetings ... Kiffi

Re: Pure csv?

Posted: Mon May 21, 2012 9:46 pm
by epog10
Well, that certainly got me started! Thank you very much.

However it is awfully slow. It is taking over 8 seconds to download the given line(s).

True, that the file is being saved and then reloaded for processing but a readfile test suggests the problem is with the download.

On my existing VB program I am downloading a single csv with about twelve items and it all happens within two seconds.

Regards,

Ernest

Re: Pure csv?

Posted: Mon May 21, 2012 10:09 pm
by TerryHough
Here is some old code of mine when I was playing with the Yahoo Finance API. It may help you out a bit.

Code: Select all

If OpenWindow(0, 0, 0, 340, 460, "Yahoo Quotes", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  WebGadget(0, 0, 0, 340, 460, "file://C:\YahooQuotes.html")
  ; Note: if you want to use a local file, change last parameter to "file://" + path + filename
  Result$ = GetGadgetItemText(0, 1)
  ;MessageRequester("DB",Result$)
  Repeat
  Until WaitWindowEvent() = #PB_Event_CloseWindow
EndIf
End 
And the related HTML file

Code: Select all

<!doctype html public "-//w3c//dtd html 3.2//en">

<html>
	<head>
		<title>
                        Yahoo Quotes
		</title>
	</head>
	<body bgcolor="#ffffff" text="#400040" link="#00ff80" vlink="#008080" alink="#8080ff">
		<COMMENT>
			<h4>
				Stock Quotes from Yahoo!
			</h4>
			<form action="http://finance.yahoo.com/q" method=get>
				Enter one 
				<strong>
					or more
				</strong>
				 ticker symbols, Or 
				<a href="http://finance.yahoo.com/l">look up the symbol</a>
				 by company name. 
				<br>
				<input type=text name=s size=30>
				<input type=submit value="Get Quotes">
				<Select name=d>
					<option value=v1 selected>
					Basic
					<option value=v2>
					DayWatch
					<option value=v4>
					Fundamentals
					<option value=t>
					Detailed
					<option value=1y>
					Chart
					<option value=r>
					Research
				</Select>
			</form>
		</COMMENT>
		<!-- Start of Yahoo! Finance code -->
		<iframe allowtransparency="true" marginwidth="0" marginheight="0" hspace="0" vspace="0" frameborder="0" scrolling="no" src="http://api.finance.yahoo.com/instrument/1.0/BIP,C,GLAD,INSP,INTU,HIMX,HRB,MO,SILU,SJM,PODD,WFC/badge;quote/HTML?AppID=sxO5EJzecAfi8umzJDWx7MJDXQ--&sig=XJr1tONv5AGXijlcd9Hi5_NBHkY-&t=1225995976625" width="360px" height="800px">
		</iframe>
		<!-- End of Yahoo! Finance code -->
	</body>
</html>

Re: Pure csv?

Posted: Tue May 29, 2012 8:20 pm
by epog10
Where does time go?

Thank you very much for your responses. Unfortunately they seem to raise more questions (probably because of my lack of knowledge) than answers!

I was particularly interesed in the csv import because it is well documented and the ease with which the data can be manipulated. Unfortunately the ReceiveHTTPFile function takes much too long if you are wishing to sample at a frequency within a minute.

The other response does have the magic term 'API'. All I know about this is that it is a way of sending a structured request and receiving a structured reply that can be easily analyzed. It is dependent on the source site defining the structure of the interactions.

And there is the problem! With a load time within a couple of seconds, the example given does show promise. Looking at the request file, not much of it makes sense to me but presumably I will be able to convert it once I get the Yahoo documentation. Unfortunately searching for 'Yahoo' and 'financial' and 'API' simply returns some pages to do with RSS feeds!

The MessageRequester by the way does not return anything. I thought it might be in the wrong place so I played around with various formats to no avail.

Never mind I thought, perhaps all I need to do is set up a portfolio, then do a straight-forward page request and scrape the data. Whatever I try, the extraction of the data just does not happen!

Here is some test code I have been using:-

Code: Select all

Enumeration
  #Web_1
  #Button_1
EndEnumeration

Global aNS.s

If OpenWindow(0, 0, 0, 420, 400, "WebGadget", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  ANS$ = "http://www.igindex.co.uk/pricestream/outside/getMarketList?webSiteId=igi&businessArea=F&locale=en_GB&firstTime=true"   
  ButtonGadget(#Button_1, 350, 150, 50, 20, "Go")
  WebGadget(#Web_1, 10, 10, 400, 130, ANS$)

  Repeat
    EventID = WaitWindowEvent()
    If EventID = #PB_Event_Gadget
      Select EventGadget()
        Case  #Button_1
          aNS = GetGadgetItemText(#Web_1, #PB_Web_SelectedText)
          MessageRequester("DB",aNS)
      EndSelect
    EndIf
  Until EventID = #PB_Event_CloseWindow
EndIf
End
So, I remain very stuck!

Regards,

Ernest

Re: Pure csv?

Posted: Tue May 29, 2012 10:05 pm
by TerryHough
Hi Ernest,
You are correct. ReceiveHTTPFile is too slow for your planned use. I don't know why, but I believe it is waiting for a timeout to be received..

Using WinXPPro on 1.8ghz system:
I have a program using ReceiveHTTPFile that takes 10.031 seconds.
The exact same program using the Windows API URLDownloadToFile_() takes .656 seconds.
And slightly modified using ts-soft's ReceiveHTTPMemory() takes .187 seconds.

The example program that follows uses the Yahoo Finance API to download CSV info for a selected set of symbols, processes the CSV info, formats, and displays it. You should be able to modify it for your use.

The desired symbols are embedded in the URL created at line 80. Substitute your own stock symbols. That is followed by the switches for the Yahoo Finance API.

You can see an explanation of those switches by running the program and clicking on the Yahoo Finance API tab.

Example program follows:

Code: Select all

; Example by terryhough for PB Forum - Using Yahoo Finance API to grab quotes from returned CSV file

Procedure.i ReceiveHTTPMemory(URL.s, BufferSize = 4096, Timeout = 5000)
  ;====================== ReceiveHTTPMemory ============================
  ;  Author:                Thomas Schulz (ts-soft)
  ;  Date:                  January 13, 2011
  ;  Target OS:             All
  ;  Target Compiler:       Requires PureBasic 4.xx
  ;=====================================================================
  
  Protected Connection, Time, Time2, Event, Size, Size2, SizeAll, pos
  Protected.s Server
  Protected *Mem, *Buffer, *Mem2
  Size = 1
  
  If LCase(Left(URL, 7)) <> "http://" : URL = "http://" + URL : EndIf
  Server = GetURLPart(URL, #PB_URL_Site)
  If Not Server : ProcedureReturn #False : EndIf
  Connection = OpenNetworkConnection(Server, 80, #PB_Network_TCP)
  If Not Connection : ProcedureReturn #False : EndIf
  If BufferSize <= 0 : BufferSize = 4096 : EndIf
  *Buffer = AllocateMemory(BufferSize)
  If Not *Buffer : ProcedureReturn #False : EndIf
  
  SendNetworkString(Connection, "GET " + URL + " HTTP/1.0" + #CRLF$ + #CRLF$)
  Time = ElapsedMilliseconds()
  Repeat
    Event = NetworkClientEvent(Connection)
    If Event = #PB_NetworkEvent_Data
      Repeat
        Size = ReceiveNetworkData(Connection, *Buffer, BufferSize)
        If Size > 0
          Time = ElapsedMilliseconds()
          SizeAll + Size
          *Mem = ReAllocateMemory(*Mem, SizeAll)
          If *Mem
            CopyMemory(*Buffer, *Mem + (SizeAll - Size), Size)
          Else
            CloseNetworkConnection(Connection)
            FreeMemory(*Buffer)
            ProcedureReturn #False
          EndIf
        EndIf
      Until Size <= 0
    EndIf
    Time2 = ElapsedMilliseconds() - Time
  Until Time2 > Timeout Or Size <= 0
  CloseNetworkConnection(Connection)
  FreeMemory(*Buffer)
  If Time2 > Timeout
    If *Mem : FreeMemory(*Mem) : EndIf
    ProcedureReturn #False
  EndIf
  pos = FindString(PeekS(*mem, -1, #PB_UTF8), #CRLF$ + #CRLF$, 1) - 1
  pos = Len(#CRLF$ + #CRLF$) + pos
  Size2 = MemorySize(*Mem) - pos
  *Mem2 = AllocateMemory(Size2)
  If *Mem2
    CopyMemory(*Mem + pos, *Mem2, Size2)
    FreeMemory(*Mem)
    ProcedureReturn *Mem2
  EndIf
  FreeMemory(*Mem)
  ProcedureReturn #False
EndProcedure

lvc.LV_COLUMN  ; set up structure for global use to format ListIconGadget columns
lvc\mask = #LVCF_FMT
lvc\fmt  = #LVCFMT_RIGHT

DownloadFilename.s = "C:\MyStocks.csv"

InitNetwork() ; Necessary in order to use PB's network function later (ReceiveHTTPMemory)

hWnd.l = OpenWindow(0,0,0,800,600,"Stock History",#PB_Window_SystemMenu|#PB_Window_ScreenCentered)
If hWnd
  PanelGadget(999,10,10,780,580)
  AddGadgetItem(999,-1,"Quote List")
  
  URL.s = "http://download.finance.yahoo.com/d/quotes.csv?s=C+GLAD+HIMX+INSP+INTU+HRB+JTX+PODD+SILU+WFC&f=snoghl1b2b3p2"
  Starttime.l = ElapsedMilliseconds()  ; Start the processing timer
  ; ---- Use ts-soft's ReceiveHTTPMemory procedure to download the file ----------------
  *mem = ReceiveHTTPMemory(URL)                                       ; fastest method
  ; ----------------------------------------------------------------------
  ; Got the file, now process it.
  If *mem
    File$ = PeekS(*mem)    
    FreeMemory(*mem)
    
    ListIconGadget(i,10,10,760,540,"Symbol",100)
    AddGadgetColumn(i, 1, "Name", 150)
    AddGadgetColumn(i, 2, "Open",  60)
    AddGadgetColumn(i, 3, "Low",   60)
    AddGadgetColumn(i, 4, "High",  60)
    AddGadgetColumn(i, 5, "Last",  60)
    AddGadgetColumn(i, 6, "Ask",   60)
    AddGadgetColumn(i, 7, "Bid",   60)
    AddGadgetColumn(i, 8, "% Chg", 60)
    LIG = GadgetID(i)
    SendMessage_(LIG, #LVM_SETCOLUMN, 2, @lvc)  ; Right justify column 2
    SendMessage_(LIG, #LVM_SETCOLUMN, 3, @lvc)  ; Right justify column 3
    SendMessage_(LIG, #LVM_SETCOLUMN, 4, @lvc)  ; Right justify column 4
    SendMessage_(LIG, #LVM_SETCOLUMN, 5, @lvc)  ; Right justify column 5
    SendMessage_(LIG, #LVM_SETCOLUMN, 6, @lvc)  ; Right justify column 6
    SendMessage_(LIG, #LVM_SETCOLUMN, 7, @lvc)  ; Right justify column 7
    SendMessage_(LIG, #LVM_SETCOLUMN, 8, @lvc)  ; Right justify column 8
    While WindowEvent():Wend ; force window to repaint
    LockWindowUpdate_(hWnd)   ; speed - lock window form updating until completely built
    j = 1
    Repeat
      DayData$ = RTrim(StringField(File$, j, #LF$), #CR$)
      j + 1
      DayData$ = RemoveString(DayData$,Chr(34))
      ; ---- Remove any commas from company name field --------------------
      Result = CountString(DayData$, ",")
      If result > 8
        pos = FindString(DayData$,",",1)
        unwanted = FindString(DayData$,",",pos+1)
        DayData$ = RemoveString(DayData$, ",", #PB_String_NoCase, unwanted, 1)
      EndIf   
      ; -------------------------------------------------------------------
      Line$ = StringField(DayData$,1,",") + #LF$
      Line$ + StringField(DayData$,2,",") + #LF$
      Line$ + StringField(DayData$,3,",") + #LF$
      Line$ + StringField(DayData$,4,",") + #LF$
      Line$ + StringField(DayData$,5,",") + #LF$
      Line$ + StringField(DayData$,6,",") + #LF$
      Line$ + StringField(DayData$,7,",") + #LF$
      Line$ + StringField(DayData$,8,",") + #LF$
      Line$ + StringField(DayData$,9,",") + #LF$
      AddGadgetItem(i, -1, Line$)
      While WindowEvent():Wend
    Until Not DayData$
    LockWindowUpdate_(#Null)  ; speed - unlock window for updates
    
  Else
    ; failure to download the file
    MessageRequester("DB","Download failure " + "Ticker")
  EndIf
  CloseGadgetList()
  
  ; Show processing time for the heck of it.
  ; MessageRequester("Time to process",StrF((ElapsedMilliseconds() - Starttime)/1000,3) + " seconds.",#MB_ICONINFORMATION)
  
  AddGadgetItem(999,-1,"Yahoo Finance API")
  URL.s = "http://elfecc.no-ip.info/purebasic/Yahoo_Finance_API.htm"
  WebGadget(998, 10, 10, 780, 590, URL)
  CloseGadgetList()
  
  Repeat
    Event = WaitWindowEvent()
  Until Event = #PB_Event_CloseWindow
  
Else
  ; failed to open window
EndIf
Hope this gives you some ideas.

Terry

Re: Pure csv?

Posted: Thu May 31, 2012 2:39 pm
by ar-s
You may have to remove CloseGadgetList() from line 140

Re: Pure csv?

Posted: Thu May 31, 2012 4:43 pm
by USCode
TerryHough wrote:Hi Ernest,
You are correct. ReceiveHTTPFile is too slow for your planned use. I don't know why, but I believe it is waiting for a timeout to be received. ...
Ernest or Terry,
Please open a bug report for the ReceiveHTTPFile issue. It shouldn't take that long, sounds like something else is going on. Fred is in turbo-bug-fix mode, let's get it on his radar!