YahooYQL.py

Go to the documentation of this file.

## @package serverportfolio.Parsers.YahooYQL
# 
# Specific parser using Yahoo YQL interface
#
# Last changed $Id: YahooYQL.py 21 2015-04-19 19:27:46Z michael $

# doc YQL
# https://developer.yahoo.com/yql/docs/
# YQL console
# https://developer.yahoo.com/yql/console/

import sys, re, copy, logging
import urllib2
from urllib import quote_plus # urlencode
import datetime, time

from lxml import html, etree

# base class
from serverportfolio.Parsers.Abstract import AbstractParser

# working, best way found to get direct access to GlobalDicts  
from serverportfolio import GlobalDicts
from serverportfolio.GlobalDicts import EAction
from serverportfolio.StockTemplates import StTmpl
from serverportfolio.PortfolioException import PortfolioError, ParserError, QueryError
from serverportfolio import Utils
from serverportfolio.Parsers import UtilsParsers

MULTI_PLUS = re.compile(r"\+{2,}")
MULTI_SPACE = re.compile(r" {2,}")

#taken from yql/utils
def clean_url(url):
    """Cleans up a uri/url"""
    url = url.replace("\n", "")
    return MULTI_PLUS.sub("+", url)

def clean_query(query):
    """Cleans up a query"""
    query = query.replace("\n", "")
    # added for tab in """ """" strings 
    query = query.replace("\t", "")
    query = MULTI_SPACE.sub(" ", query)
    return query

## @class YahooYQL
# @brief Use the Yahoo YQL API
class YahooYQL ( AbstractParser ):

    # Unique public address for all YQL queries, otherwise can register
    __YQL_PUBLIC_URL='https://query.yahooapis.com/v1/public/yql?q='

    ## @brief Constructor.
    # @param e_action EAction to perform
    def __init__(self, e_action):
        # init parent
        super(YahooYQL, self).__init__(e_action)
        
        self.logger = logging.getLogger('SP.YahooYQL')
        self.logger.debug("Constructor Parser_YahooYQL")
        
        self.source = "YQL"

    ## @brief Create the URL from a sql query.
    # table (quote/quotes/stocks/.. defined in the query)
    # all YQL queries accept multi-stocks ? seems yes
    def create_url(self):
        self.logger.debug("create_url")
        self.logger.debug("e_action: %s" % self.e_action)
        self.logger.debug("local stock: %s", self.local_stock)
        
        # only action supported by this function    
        if (self.e_action == EAction.Fundamental):
            print "define query fundamental"
            query="""
                use "http://www.datatables.org/yahoo/finance/yahoo.finance.quotes.xml";
                select * from yahoo.finance.quotes where symbol in (%s)
            """
        else:
            raise ParserError("wrong action for YahooYQL %s" % self.e_action.name, \
                              self.local_stock.keys(), self.e_action.name, 'None')
            
        str_stocks_yahoo = self._format_symbols_url()
        query = query % ( str_stocks_yahoo )
        # case of tabulated string and mutlispace, clean the string
        query=clean_query(query)
        self.logger.debug("query: %s" % query)
        self.logger.debug("encoded query: %s" % quote_plus(query) )
        
        self.url = self.__YQL_PUBLIC_URL + quote_plus(query)
        self.logger.info("url: %s" % self.url)
        
    ## @brief Only for testing, take a full sql query as input(hard-coded in main section)
    # @param sql string query
    def create_url_test(self, sql_query):
    
        self.logger.debug("YahooYQL::create_url_test")
            
        # case of tabulated string, mutlispace
        query=clean_query(sql_query)
        print "query after clean ", query 
        # query_sql='select * from yahoo.finance.quote where symbol in ("YHOO","GSZ.PA")'
        # urlib.quote_plus
        print "encoded: ", quote_plus(query)
        self.url = self.__YQL_PUBLIC_URL + quote_plus(query)
        print "self.url: ", self.url
    

    ## @brief Parse YQL request, always XML format.
    # Name of the fields are available in the XML output.
    # @param s page retrived by web_query
    def parse(self, s):
        self.logger.debug("parse()")
        # load the XML file
        root = etree.fromstring( s )
        # nice output of the XML
        self.logger.debug("%s" % etree.tostring(root, pretty_print=True, encoding='unicode'))
        
        try:
            # Info done by run_parser_info
            #if (self.e_action == EAction.Info):
                #print "EAction == Info"
            #    self.parse_info( root )
            if self.e_action == EAction.Fundamental:
                self.parse_fund( root )
            else:
                self.logger.error("wrong action in parse: %s" % self.e_action.name )
        except ParserError as ex:
            self.logger.error("parse() catch ParserError: %s", ex)
            raise
        except Exception as ex:
            self.logger.error("parse() catch Exception: %s", ex)
            raise ParserError(ex, self.local_stock.keys(), self.e_action.name, self.url)
 
    ## @brief Parse XML for info action
    # To get Industry,Sector and Name, this function is called 2 times by run_parser_info()\n
    # \warning not fully working, table for yahoo.finance.stocks deprecated !
    # @param s page retrieved by web_query
    def parse_info(self, s):
        self.logger.debug("parse_info()")
        
        new_created = False
        # first call is None, creates list of template_parser
        if self.list_return_data is None:
            self.list_return_data = list()

        # format for Info, certainly common to other quote/quotes
        # <query>
        #  <results>
        #    <stock symbol="faz"> (lower) or GSZ.PA FSLR
        # do not crash if empty e.g. CAC40 Info
        
        # load the XML file into lxml structure
        root = etree.fromstring( s )
        # nice output of the XML
        self.logger.debug("%s" % etree.tostring(root, pretty_print=True, encoding='unicode'))
         
        # loop over the stocks in xml
        for stock_xml in root[0]:
            # to assign to the list of dictionary
            new_created = True
            dict_data_stock = None
            
            symbol_yahoo = stock_xml.get("symbol")
            symbol = self.get_symbol_from_code( symbol_yahoo, 'code_yahoo' )
            self.logger.debug("symbol: %s" % symbol)
             
            # to be sure at least one element is added, crash(later ?) if toto alone, CAC40 is ok
            # should check about symbol,action to be more specific
            for elem in self.list_return_data:
                
                if elem['symbol'] == symbol:
                    dict_data_stock = elem
                    self.logger.debug("found symbol in list_return_data break")
                    new_created = False
                    break
                    
            if new_created:
                # first call create the template parser and the action
                self.logger.debug("symbol not found in dict_return_data, create a new template parser")
                #dict_data_stock = None
                dict_data_stock = StTmpl.get_template_parser( self.e_action.name )
                dict_data_stock['symbol'] = symbol
                self.list_return_data.append( dict_data_stock )

            # exist for sure
            self._fill_dict_from_xml( stock_xml, dict_data_stock['action_templ'][self.e_action.name] )

    ## @brief Extract fundamental data from the XML root element
    # @param root XML root element
    def parse_fund(self, root):
        self.logger.debug("parse_fund")

        self.list_return_data = list()
        
        for stock_xml in root[0]:
            # can check if an error is present, looking for the tag.
            # should easily save it from here
            error_code = stock_xml.find('ErrorIndicationreturnedforsymbolchangedinvalid').text
            if error_code != None:
                self.logger.warning("error code in YahooYQL:parse_fund %s" % error_code)
                continue
            
            # get symbol of the stock, yahoo code is always present in output
            symbol_yahoo = stock_xml.get("symbol")
            symbol = self.get_symbol_from_code( symbol_yahoo, 'code_yahoo' )
            self.logger.debug("symbol: %s" % symbol)
            # assign template and symbol
            dict_data_stock = StTmpl.get_template_parser( self.e_action.name )
            dict_data_stock['symbol'] = symbol
            
            # make shortcut
            tmpl_action = dict_data_stock['action_templ'][ self.e_action.name ]
            # fill tmp_dict with values from stock_xml
            self._fill_dict_from_xml( stock_xml, tmpl_action )
            
            # add this stock to the list
            self.list_return_data.append( dict_data_stock )

    ## @brief Specific call to get Info.
    # This version should be called at the place of run_parser only in the case of the EAction.Info with YahooYQL.\n
    # Two parsing are needed to fill the template.
    # @param stock list of stock symbol(s)
    def run_parser_info(self, stock, option_post=None):
        self.logger.debug("Entry run_parser_info")
        
        if option_post != None:
            self.option_post = option_post
            self.logger.debug('option_post is present: %s' % option_post)
            
        try :
            self.store_stock_copy( Utils.to_list(stock) )
            # could return an exception... to see with post-processing
            if len(self.local_stock) == 0:
                self.logger.error("No valid Stock after store_stock_copy")
                return
        except Exception as ex:
            self.logger.debug("Caught Exception in store_stock_copy: ex", ex)
            raise
        
        # first_url, get Sector/Industry
        query = """
                use "http://www.datatables.org/yahoo/finance/yahoo.finance.stocks.xml";
                select * from yahoo.finance.stocks where symbol in (%s)
            """
        str_stocks_yahoo = self._format_symbols_url()
        query = query % ( str_stocks_yahoo )
        
        # case of tabulated string and mutlispace
        query=clean_query(query)
        self.url = self.__YQL_PUBLIC_URL + quote_plus(query)
        self.logger.info("url: %s" % self.url)
        
        # first query
        s = None
        try :
            s = UtilsParsers.web_query( self.url )
        # now correct QueryError
        except QueryError as ex:
            self.logger.error("Got a QueryError from web_query %s" % ex ) 
            raise
        # here crash
        except Exception as ex:
            self.logger.error("Got a general Exception from web_query %s" % ex )
            # should raise a QueryError normally
            raise ParserError( ex, self.local_stock.keys(), self.e_action.name, self.url )
        
        # first parsing
        # try
        self.parse_info(s)

        # second parsing, quote
        query="""
            use "http://www.datatables.org/yahoo/finance/quote/yahoo.finance.quote.xml";
            select * from yahoo.finance.quote where symbol in  (%s)
        """
        #str_stocks_yahoo = None
        str_stocks_yahoo = self._format_symbols_url()
        query = query % ( str_stocks_yahoo )
        #print "intermediate query", query
        # case of tabulated string and mutlispace
        query=clean_query(query)
        self.logger.debug("query: %s" % query)
        self.logger.debug("encoded query: %s" % quote_plus(query) )
        
        self.url = self.__YQL_PUBLIC_URL + quote_plus(query)
        self.logger.info("url: %s" % self.url)
        
        # second query
        s = None
        self.logger.debug("web_query")
        try :
            s = UtilsParsers.web_query( self.url )
        except Exception as ex:
            self.logger.error("Got a general Exception from web_query %s" % ex )
            # should raise a QueryError normally
            raise ParserError( ex, self.local_stock.keys(), self.e_action.name, self.url )
        
        # second parsing, same function
        # try
        # original, call the same, but sector/industry not present !
        self.parse_info(s)

        # update the stock
        self.update_stock()

# could be static(except logger), or in utils

    ## @brief Fill a dictionary template from the tags in the XML.
    # All unknown entries are stored in an 'other' sub-dictionary.\n     
    # @param stock_xml lxml element as input
    # @param dict_stock dictionary to be filled as output
    def _fill_dict_from_xml(self, stock_xml, dict_stock):
        self.logger.debug("fill_dict_from_xml")
        self.logger.debug("size stock_xml %d" % len(stock_xml) )
        #self.logger.debug("%s" % etree.tostring(stock_xml, pretty_print=True, encoding='unicode'))
        
        for elem in stock_xml:
            #print "elem.tag ", elem.tag
            #print "elem.text ", elem.text
            # entry is fixed in template
            if elem.tag in dict_stock:
                # print "Fixed entry"
                # need to clean, check if empty or other
                dict_stock[ elem.tag ] = elem.text
            # assign to other, to test if 'other' exists in a specific templ_dict
            else:
                # print "Assign to other"
                dict_stock['other'][ elem.tag ] = elem.text
         
# from YahooCSV, to delete function or replace or utils.parsers
# code_yahoo = ','.join( stock.get_action('Static','code_yahoo') for stock in self.local_stock.values() )    
    def _format_symbols_url(self):
        compt = 0
        str_stocks_yahoo = str()
        for stock_symbol in self.local_stock:
            str_stocks_yahoo += '"' + self.local_stock[stock_symbol].get_action('Static','code_yahoo') + '"'

            if compt < len(self.local_stock)-1:
                str_stocks_yahoo += ','
            compt += 1
            
        self.logger.debug("string stocks for url:%s",str_stocks_yahoo)
        return str_stocks_yahoo
        
# ####### Main for test only
# Implemented Info from yahoo.finance.stocks.xml:
#   - Sector
#   - Industry
# But NameCompany is not present ! 
# Can get from yahoo.finance.quote/quotes, quotes more complete or from any HTML page(maybe tricky)
#
# Can get from YahooCSV Fundamental, parsing more tricky move to Fundamental not very logic, or deal in update...
#
# Can merge Info/Fundamental or make 2 parsing for Info only. 
# Update Name/Sector/Industry to do only once ! update info to write in YQL !
# 
# todo add arguments (normal use Stock Action) and option to test simple sql query, partly done
if __name__ == "__main__":
    
    print "YahooYQL main"
    
    from serverportfolio.DictionaryStocks import DictionaryStocks
    import UtilsParsers
    import types, traceback
    #print "End import"
    
    logging.basicConfig(level=logging.DEBUG)
    # logging.basicConfig(filename='myapp.log', level=logging.INFO) # filemode='w' to not append to file
    logging.getLogger('SP')
    m_logger = logging.getLogger("SP.main")

    # call not needed, loaded on demand by parsers or stocks
    #dict_stocks = DictionaryStocks()
    
    try:
        m_stock = sys.argv[1]
        m_action = sys.argv[2]
    except IndexError:
        print " Only for debugging, should call ./Run_Parsers for all options"
        print " Usage:"
        print " YahooYQL FSLR,GSZ Info [-opt_yql]"
        print " Action: Info / Fundamental"
        print " optional -opt_yql to run a fixed(hard-coded) sql query and print the XML only, for testing"
        sys.exit(1)
        
    # transform to a list, from format "Stock1,Stock2,.."
    m_list_stock = m_stock.split(',')
    
    try :
        if type(m_action) == types.StringType:
            m_e_action = EAction[m_action]
        # isisntance(EAction)
        else:
            m_e_action = m_action
    except :
        m_logger.error("Exception from EAction enumeration, cannot assign an e_action from action: %s" % m_action)
        sys.exit(1)
    
    # read option, should test argument 1
    m_option_sql = str()
    if len(sys.argv) == 4:
        m_option_sql = sys.argv[3]
        
    m_opt_sql = False
    if m_option_sql == '-opt_sql':
        m_opt_sql = True
    print "opt_sql ", m_opt_sql

# test with opt_sql use an explicit sql request   
    if m_opt_sql:
        print "\n== Test only SQL query"
        
        # action not used. At least can get the XML file to compare
        m_parser = YahooYQL( EAction.Info )
        
        # example use finance.stocks.xml table
        #query_sql="""
        #    use "http://www.datatables.org/yahoo/finance/yahoo.finance.stocks.xml";
        #    select * from yahoo.finance.stocks where symbol in ("GSZ.PA","FSLR")
        #"""
        
        # example use finance.quotes.xml
        #query_sql="""
        #    use "http://www.datatables.org/yahoo/finance/yahoo.finance.quotes.xml";
        #    select * from yahoo.finance.quotes where symbol in ("GSZ.PA","FSLR")
        #"""
        
        # example use finance.quote.xml, less output, a filter is applied
        # can be used for Info
        m_query_sql="""
            use "http://www.datatables.org/yahoo/finance/quote/yahoo.finance.quote.xml";
            select * from yahoo.finance.quote where symbol in ("GSZ.PA","FSLR")
        """
        
        m_parser.create_url_test( m_query_sql )
        print "m_parser.url ", m_parser.url
        
        try:
            m_s = UtilsParsers.web_query( m_parser.url )
            
        except PortfolioError as ex:
            print "Catch PortfolioError from web_query"
            print "ex: ", ex
            print "get_format_string\n", ex.get_format_string()
            sys.exit(1)
            
        except Exception as ex:
            print "Catch exception from web_query"
            print "ex: ", ex
            #print "get_format_string ", ex.get_format_string()
            sys.exit(1)
            
        print m_s
        
        # at least can get formatted XML to see available data
        # general load the XML file
        m_root = etree.fromstring( m_s )
        #print "XML pretty:"
        print(etree.tostring(m_root, pretty_print=True, encoding='unicode'))
        
        # exit after execution
        sys.exit(0)
 
# normal run with Info or Fundamental
    m_parser = YahooYQL( m_e_action )
        
    try :
        if m_e_action == EAction.Info:
            m_parser.run_parser_info( m_list_stock )
        else:
            m_parser.run_parser( m_list_stock )
            
    except PortfolioError as ex:
        print "Caught PortfolioError from run_parser_info()"
        print ex.get_format_string()                   
    except Exception as ex:
            m_logger.debug("Caught Exception from store_stock_copy: %s", ex)
            raise
            
    # simple output 
    for stock in m_list_stock:
        print Utils.pretty_dict( DictionaryStocks().get_stocks( stock ).get_action() )
        
    # can skip the config explicitly or include in pretty_dict
    #for stock in m_list_stock:
    #    print Utils.pretty_dict( DictionaryStocks().get_stocks( stock ).get_action(m_e_action.name) )
        
    sys.exit(0)
    
    # Example from console
    # desc yahoo.finance.quotes
    #url='https://query.yahooapis.com/v1/public/yql?q=desc%20yahoo.finance.quotes&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys'
    # same with json, default XML
    #url='https://query.yahooapis.com/v1/public/yql?q=desc%20yahoo.finance.quotes&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback='

    # quote table
    #url='https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quote%20where%20symbol%20in%20(%22YHOO%22%2C%22GSZ%22)&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys'
    # without additional info, Table not found, GSZ code is wrong, should be GSZ.PA, good for test
    # last test working url + url+env
    #url='https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quote%20where%20symbol%20in%20(%22YHOO%22%2C%22GSZ%22)'

    # test1, ok works with env use all tables
    # query = 'https://query.yahooapis.com/v1/public/yql?q='
    # test test2 specify the table to use in SQL request, ok working also
    # query_sql='use "http://www.datatables.org/yahoo/finance/quote/yahoo.finance.quote.xml" as foo;select * from foo where symbol in ("YHOO","GSZ.PA")'
    # CreateQuerySQL
    # working in test version
    # query_sql="""
    #    use "http://www.datatables.org/yahoo/finance/quote/yahoo.finance.quote.xml" as foo;
    #    select * from foo where symbol in ("YHOO","GSZ.PA")
    #"""
    # ok in test version
    #query_sql="""
    #    use "http://www.datatables.org/yahoo/finance/yahoo.finance.stocks.xml";
    #    select * from yahoo.finance.stocks where symbol="%s"
    #"""
    
    # for test1
    # example, http://stackoverflow.com/questions/16783175/yql-no-definition-found-for-table
    # to load all Open table: env="store://datatables.org/alltableswithkeys"
    # normal use with env= in url, here use all tables
    #url+='&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys'

    # test2, table to use in the sql command, could use other table, split use and table and query
    # allow to test more OpenTable quote/quotes/stocks, 
    # query = 'use "http://www.datatables.org/yahoo/finance/yahoo.finance.option_contracts.xml" as foo;
    #          SELECT * FROM foo WHERE symbol="SPY"'