2 Replies Latest reply: Feb 20, 2012 11:37 PM by Billy~Verreynne RSS

    requesting financial data from yahoo site

    875962
      i am trying to write a program which would populate data from yahoo financial site.

      i have written a program which would ask for company symbol and start and end year. program which i created does ask for required parameters but populated entire histrionically data of respective company . please help me with it.

      SET serveroutput on size 500000
      SET linesize 200
      ACCEPT s_sp PROMPT 'Enter Company: '
      ACCEPT c_sp PROMPT 'start_year: '
      ACCEPT f_sp PROMPT 'end_year: '

      PROMPT
      SHOW user
      PROMPT Retrieving Company !s_sp
      PROMPT Retrieving start_year !c_sp
      PROMPT Retrieving end_year !f_sp

      DECLARE
      -- delcare varliables
      req UTL_HTTP.REQ;
      resp UTL_HTTP.RESP;
      value VARCHAR2(1024);
      tic VARCHAR2(4);
      start_year varchar2(4);
      end_year varchar2(4);
      BEGIN

      tic := '!s_sp';
      start_year := '!c_sp';
      end_year := '!f_sp';
      req := UTL_HTTP.BEGIN_REQUEST('http://ichart.finance.yahoo.com/table.csv?s=' ||
      tic ||'&a=01&b=1&c=!&d=12&e=31&f=!&g=d&ignore=.csv');
      UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
      resp := UTL_HTTP.GET_RESPONSE(req);
      LOOP
      UTL_HTTP.READ_LINE(resp, value, TRUE);
      DBMS_OUTPUT.PUT(tic || ',');
      DBMS_OUTPUT.PUT_LINE(value);
      END LOOP;
      UTL_HTTP.END_RESPONSE(resp);

      EXCEPTION
      WHEN UTL_HTTP.END_OF_BODY THEN
      UTL_HTTP.END_RESPONSE(resp);
      END;
      /
        • 1. Re: requesting financial data from yahoo site
          523861
          the problem you have is not with PL/SQL, but with the webservice results from yahoo.

          refine your web query in a browser first, then apply it to your stored proc that calls that service.
          • 2. Re: requesting financial data from yahoo site
            Billy~Verreynne
            Write PL/SQL procedures, functions and packages. Not client-side scripts using anonymous PL/SQL blocks. And use the features, such as object types (advance data types) and pipeline table functions - as this can provide a seamless interface.

            Here is such an example:
            // we need an object type for a collection of strings
            SQL> create or replace type TStrings is table of varchar2(4000);
              2  /
            
            Type created.
            
            // as the web service returns a CSV line, we need a basic parser that
            // parses the CSV line and returns the tokens in the line (regular expressions
            // is another option to consider)
            SQL> create or replace function Tokenise(
              2          csvLine varchar2,
              3          separator varchar2 default ',',
              4          enclosedBy varchar2 default null
              5  ) return TStrings AUTHID CURRENT_USER is
              6          strList         TStrings;
              7          str             varchar2(32767);
              8          i               integer;
              9          l               integer;
             10          enclose1        integer;
             11          enclose2        integer;
             12          encloseStr      varchar2(4000);
             13          replaceStr      varchar2(4000);
             14  
             15          procedure AddString( line varchar2 ) is
             16          begin
             17                  strList.Extend(1);
             18                  strList( strList.Count ) := Replace( line, CHR(0), separator );
             19          end;
             20  
             21  begin
             22          strList := new TStrings();
             23  
             24          str := csvLine;
             25          loop
             26                  if enclosedBy is not null then
             27                          -- find the ennclosed text, if any
             28                          enclose1 := InStr( str, enclosedBy, 1 );
             29                          enclose2 := InStr( str, enclosedBy, 2 );
             30  
             31                          if (enclose1 > 0) and (enclose2 > 0) and (enclose2 > enclose1) then
             32                                  -- extract the enclosed string
             33                                  encloseStr := SubStr( str, enclose1, enclose2-enclose1+1 );
             34                                  -- replace the separator char's with zero char's
             35                                  replaceStr := Replace( encloseStr, separator, CHR(0) );
             36                                  -- and remove the enclosed quotes
             37                                  replaceStr := Replace( replaceStr, enclosedBy );
             38                                  -- change the enclosed string in the big string to the replacement string
             39                                  str := Replace( str, encloseStr, replaceStr );
             40                          end if;
             41                  end if;
             42  
             43                  l := Length( str );
             44                  i := InStr( str, separator );
             45  
             46                  if i = 0 then
             47                          AddString( str );
             48                  else
             49                          AddString( SubStr( str, 1, i-1 ) );
             50                          str := SubStr( str, i+1 );
             51                  end if;
             52  
             53                  -- if the separator was on the last char of the line, there is
             54                  -- a trailing null column which we need to add manually
             55                  if i = l then
             56                          AddString( null );
             57                  end if;
             58  
             59                  exit when str is NULL;
             60                  exit when i = 0;
             61          end loop;
             62  
             63          return( strList );
             64  end;
             65  /
            
            Function created.
            
            // need an object type for the data returned by the web service
            SQL> create or replace type TSharePrice is object(
              2          day             date,
              3          open_price      number,
              4          high_price      number,
              5          low_price       number,
              6          close_price     number,
              7          volume_traded   number,
              8          adj_close       number
              9  );
             10  /
            
            Type created.
            
            // also need a table/collection type as the web service will supply multiple
            // copies of the data
            SQL> create or replace type TSharePriceTable is table of TSharePrice;
              2  /
            
            Type created.
            
            // use a pipeline table function that transforms the web service into what looks and 
            // acts like a SQL table
            SQL> create or replace function SharePrice( symbol varchar2, startDate date, endDate date, intervalType varchar2 default 'd' )
              2          return TSharePriceTable pipelined is
              3  
              4          CSV_URL constant varchar2(1000) :=
              5          'http://ichart.finance.yahoo.com/table.csv?s=#SYMBOL#'||
              6          '&a=#START_MONTH#&b=#START_DAY#&c=#START_YEAR#'||
              7          '&d=#END_MONTH#&e=#END_DAY#&f=#END_YEAR#'||
              8          '&g=#INTERVAL#';
              9  
             10          -- IE9/Windows7 generic browser signature
             11          C_USER_AGENT    constant varchar2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
             12  
             13          -- set if web access is via a proxy server
             14          proxyServer varchar2(20);
             15          proxyUser varchar2(20);
             16          proxyPass varchar2(20);
             17  
             18          -- our local variables
             19          line            integer;
             20          proxyURL        varchar2(4000);
             21          request         utl_http.req;
             22          response        utl_http.resp;
             23          buffer          varchar2(32767);
             24          eof             boolean;
             25          url             varchar2(1000);
             26          token           TStrings;
             27          sharePrice      TSharePrice;
             28  begin
             29          -- our "browser" settings
             30          utl_http.set_response_error_check( true );
             31          utl_http.set_detailed_excp_support( true );
             32          utl_http.set_cookie_support( true );
             33          utl_http.set_transfer_timeout( 300 );
             34          utl_http.set_follow_redirect( 3 );
             35          utl_http.set_persistent_conn_support( true );
             36  
             37          -- configure for proxy access if applicable
             38          if proxyServer is not null then
             39                  proxyURL := 'http://'||proxyServer;
             40                  if (proxyUser is not null) and (proxyPass is not null) then
             41                          proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
             42                  end if;
             43                   utl_http.set_proxy( proxyURL, null );
             44          end if;
             45  
             46          url := CSV_URL;
             47          url := Replace( url, '#SYMBOL#', symbol );
             48          url := Replace( url, '#START_MONTH#', to_number(to_char(startDate,'mm'))-1 );
             49          url := Replace( url, '#START_DAY#', to_char(startDate,'dd') );
             50          url := Replace( url, '#START_YEAR#', to_char(startDate,'yyyy') );
             51          url := Replace( url, '#END_MONTH#', to_number(to_char(endDate,'mm'))-1 );
             52          url := Replace( url, '#END_DAY#', to_char(endDate,'dd') );
             53          url := Replace( url, '#END_YEAR#', to_char(endDate,'yyyy') );
             54          url := Replace( url, '#INTERVAL#', intervalType );
             55  
             56          request := utl_http.begin_request( url, 'GET', utl_http.HTTP_VERSION_1_1 );
             57          utl_http.set_header( request, 'User-Agent', C_USER_AGENT );
             58          response := utl_http.get_response( request );
             59  
             60          eof := false;
             61          line := 0;
             62          loop
             63                  exit when eof;
             64                  begin
             65                          utl_http.read_line( response, buffer, true );
             66                          line := line + 1;
             67  
             68                          if line > 1 and buffer is not null and length(buffer)>0 then
             69                                  token := Tokenise( buffer, ',' );
             70                                  if token.Count = 7  then
             71                                          pipe row(
             72                                                  TSharePrice(
             73                                                          to_date( token(1), 'yyyy-mm-dd' ),
             74                                                          to_number( token(2) ),
             75                                                          to_number( token(3) ),
             76                                                          to_number( token(4) ),
             77                                                          to_number( token(5) ),
             78                                                          to_number( token(6) ),
             79                                                          to_number( token(7) )
             80                                                  )
             81                                          );
             82                                  end if;
             83                          end if;
             84  
             85                  exception when utl_http.END_OF_BODY then
             86                          eof := true;
             87                  end;
             88  
             89          end loop;
             90          utl_http.end_response( response );
             91  
             92          return;
             93  end;
             94  /
            
            Function created.
            
            SQL> 
            Now we have a pipeline table that we can use via SQL - and the pipeline transforms CSV data output by a web service into structured data. E.g.
            // use the pipeline to get MSFT (Microsoft) share prices for Jan 2012
            SQL> select * from TABLE( SharePrice('MSFT', to_date('2012-01-01','yyyy-mm-dd'), to_date('2012-01-31','yyyy-mm-dd') ) );
            
            DAY                 OPEN_PRICE HIGH_PRICE  LOW_PRICE CLOSE_PRICE VOLUME_TRADED  ADJ_CLOSE
            ------------------- ---------- ---------- ---------- ----------- ------------- ----------
            2012/01/31 00:00:00      29.66       29.7      29.23       29.53      50572400      29.34
            2012/01/30 00:00:00      28.97      29.62      28.83       29.61      51114800      29.42
            2012/01/27 00:00:00      29.45      29.53      29.17       29.23      44187700      29.04
            2012/01/26 00:00:00      29.61       29.7       29.4        29.5      49102800      29.31
            2012/01/25 00:00:00      29.07      29.65      29.07       29.56      59231700      29.37
            2012/01/24 00:00:00      29.47      29.57      29.18       29.34      51703300      29.15
            2012/01/23 00:00:00      29.55      29.95      29.35       29.73      76078100      29.54
            2012/01/20 00:00:00      28.82      29.74      28.75       29.71     165902900      29.52
            2012/01/19 00:00:00      28.16      28.44      28.03       28.12      74053500      27.94
            2012/01/18 00:00:00      28.31       28.4      27.97       28.23      64860600      28.05
            2012/01/17 00:00:00       28.4      28.65      28.17       28.26      72395300      28.08
            2012/01/13 00:00:00      27.93      28.25      27.79       28.25      60196100      28.07
            2012/01/12 00:00:00      27.87      28.02      27.65          28      49370800      27.82
            2012/01/11 00:00:00      27.43      27.98      27.37       27.72      65582400      27.54
            2012/01/10 00:00:00      27.93      28.15      27.75       27.84      60014400      27.66
            2012/01/09 00:00:00      28.05       28.1      27.72       27.74      59706800      27.56
            2012/01/06 00:00:00      27.53      28.19      27.53       28.11      99455500      27.93
            2012/01/05 00:00:00      27.38      27.73      27.29       27.68      56081400       27.5
            2012/01/04 00:00:00      26.82      27.47      26.78        27.4      80516100      27.22
            2012/01/03 00:00:00      26.55      26.96      26.39       26.77      64731500      26.59
            
            20 rows selected 
            
            SQL> 
            Edited by: Billy Verreynne on Feb 21, 2012 5:34 AM

            Fixed the month parameter as the API uses 0 for Jan and 11 for Dec