This discussion is archived
2 Replies Latest reply: Feb 20, 2012 9:37 PM by BillyVerreynne RSS

requesting financial data from yahoo site

875962 Newbie
Currently Being Moderated
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
    WhiteHat Expert
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points