As we are interested in real time data we will use tweepy’s streaming API. Details on this are available in the tweepy documentation here http:\/\/tweepy.readthedocs.org\/en\/v3.5.0\/streaming_how_to.html<\/a>.<\/p>\n\n\n\n If you don’t already have tweepy installed, you can install it using pip<\/p>\n\n\n\n The code from this blog is available on github https:\/\/github.com\/pyxll\/pyxll-examples\/tree\/master\/twitter<\/a>.<\/p>\n\n\n\n In order to access Twitter Streaming API you will need a Twitter API key, API secret, Access token and Access token secret. Follow the steps below to get your own access tokens.<\/p>\n\n\n\n To start with we can create a simple listener class that simply prints tweets as they arrive<\/p>\n\n\n\n If we run this code any tweets mentioning Python, Excel or PyXLL get printed:<\/p>\n\n\n\n In order to make this more suitable for getting these tweets into Excel we will now extend this TwitterListener class in the following ways:<\/p>\n\n\n\n The updated TwitterListener class is as follows:<\/p>\n\n\n\n When this is run it’s very similar to the last case, except that now only the text part of the tweets are printed. Also note that the listener is not explicitly disconnected, that happens automatically when the last subscriber unsubscribes.<\/p>\n\n\n\n Now the hard part of getting the streaming twitter data into Python is taken care of, creating a real time data source in Excel using PyXLL is pretty straightforward.<\/p>\n\n\n\n PyXLL 3.0 has a new class, We will write a new class inheriting from RTD that acts as a subscriber to our twitter stream (in the same way as TestSubscriber in the code above). Whenever a new tweet is received it will update its value, and so the cell in Excel will update.<\/p>\n\n\n\n To expose that to Excel all that’s needed is a function that returns an instance of our new TwitterRTD class<\/p>\n\n\n\n All that’s required now is to add the module to the pyxll.cfg file, and then the new function ‘twitter_listen’ will appear in Excel, and calling it will return a live stream of tweets.<\/p>\n\n\n\n So far we’ve got live tweets streaming into Excel, which is pretty cool, but only one tweet is visible at a time and we can only see the tweet text. It would be even better to see a grid of data showing the most recent tweets with some metadata as well as the tweet itself.<\/p>\n\n\n\n RTD functions always return just a single cell of data, so what we need to do is write a slightly different function that takes a couple more arguments: A key for the part of the tweet we want (e.g. ‘text’ or ‘created_at’) and an index (e.g. 0 as the latest tweet, 1 the second most recent tweet etc).<\/p>\n\n\n\n As some interesting bits of metadata are in nested dictionaries in the twitter data, the ‘key’ used to select the item from the data dictionary is a ‘\/’ delimited list of keys used to drill into tweet data (for example, the name of the user is in the sub-dictionary ‘user’, so to retrieve it the key ‘user\/name’ would be used).<\/p>\n\n\n\n The TwitterListener class we’ve written already keeps a limited history of the tweets it’s received so this isn’t too much more than we’ve already done.<\/p>\n\n\n\n The worksheet function also has to be updated to take these extra arguments<\/p>\n\n\n\n After reloading the PyXLL addin, or restarting Excel, we can now call this modified function with different values for row and key to build an updating grid of live tweets.<\/p>\n\n\n\n One final step is to make sure that any active streams are disconnected when Excel closes. This will prevent the tweepy background thread from preventing Excel from exiting cleanly.<\/p>\n\n\n\n The code from this blog is available on github https:\/\/github.com\/pyxll\/pyxll-examples\/tree\/master\/twitter<\/a>.<\/p>\n\n\n\n Of course getting real time tweets into Excel is interesting, but it may not really be that useful! Real time data in Excel has many other real-world applications however, for example:<\/p>\n\n\n\n\npip install tweepy\n<\/pre><\/pre>\n\n\n\n
Getting Twitter API keys<\/h2>\n\n\n\n
Streaming Tweets in Python<\/h2>\n\n\n\n
\nfrom tweepy.streaming import StreamListener\nfrom tweepy import OAuthHandler\nfrom tweepy import Stream\nimport logging\n\n_log = logging.getLogger(__name__)\n\n# User credentials to access Twitter API\naccess_token = "YOUR ACCESS TOKEN"\naccess_token_secret = "YOUR ACCESS TOKEN SECRET"\nconsumer_key = "YOUR CONSUMER KEY"\nconsumer_secret = "YOUR CONSUMER KEY SECRET"\n\nclass TwitterListener(StreamListener):\n\n def __init__(self, phrases):\n auth = OAuthHandler(consumer_key, consumer_secret)\n auth.set_access_token(access_token, access_token_secret)\n self.__stream = Stream(auth, listener=self)\n self.__stream.filter(track=phrases, async=True)\n\n def disconnect(self):\n self.__stream.disconnect()\n\n def on_data(self, data):\n print(data)\n\n def on_error(self, status):\n print(status)\n\nif __name__ == '__main__':\n import time\n logging.basicConfig(level=logging.INFO)\n\n phrases = ["python", "excel", "pyxll"]\n listener = TwitterListener(phrases)\n\n # listen for 60 seconds then stop\n time.sleep(60)\n listener.disconnect()\n<\/pre><\/pre>\n\n\n\n
\npython twitterxl.py\n\nINFO:requests.packages.urllib3.connectionpool:Starting new HTTPS connection (1): stream.twitter.com\n{"text": "Excel keyboard shortcut - CTRL+1 to bring up Cell Formatting https:\/\/t.co\/wvx634EpUy", "is...\n{"text": "Excel Tips - What If Analysis #DMZWorld #Feature #Bond #UMI https:\/\/t.co\/lxzgZnIItu #UMI",...\n{"text": "How good are you at using #Excel? We're looking for South Africa's #ExcelChamp Ts &amp;amp;amp;amp;amp; Cs...\n{"text": "The Best Data Scientists Run R and Python - insideBIGDATA https:\/\/t.co\/rwty058dL2 #python ...\n{"text": "How to Create a Pivot Table in Excel: A Step-by-Step Tutorial (With Video) \\u2013 https:\/\/...\n{"text": "Python eats Alligator 02, Time Lapse Speed x6 https:\/\/t.co\/3km8I92zJo", "is_quote_status":...\n...\n\nProcess finished with exit code 0\n<\/pre><\/pre>\n\n\n\n\nclass TwitterListener(StreamListener):\n """tweepy.StreamListener that notifies multiple subscribers when\n new tweets are received and keeps a buffer of the last 100 tweets\n received.\n """\n __listeners = {} # class level cache of listeners, keyed by phrases\n __lock = threading.RLock()\n __max_size = 100\n\n @classmethod\n def get_listener(cls, phrases, subscriber):\n """Fetch an TwitterListener listening to a set of phrases and subscribe to it"""\n with cls.__lock:\n # get the listener from the cache or create a new one\n phrases = frozenset(map(str, phrases))\n listener = cls.__listeners.get(phrases, None)\n if listener is None:\n listener = cls(phrases)\n cls.__listeners[phrases] = listener\n\n # add the subscription and return\n listener.subscribe(subscriber)\n return listener\n\n def __init__(self, phrases):\n """Use static method 'get_listener' instead of constructing directly"""\n _log.info("Creating listener for [%s]" % ", ".join(phrases))\n self.__phrases = phrases\n self.__subscriptions = set()\n self.__tweets = [None] * self.__max_size\n\n # listen for tweets in a background thread using the 'async' keyword\n auth = OAuthHandler(consumer_key, consumer_secret)\n auth.set_access_token(access_token, access_token_secret)\n self.__stream = Stream(auth, listener=self)\n self.__stream.filter(track=phrases, is_async=True)\n self.__connected = True\n\n @property\n def tweets(self):\n return list(self.__tweets)\n\n def subscribe(self, subscriber):\n """Add a subscriber that will be notified when new tweets are received"""\n with self.__lock:\n self.__subscriptions.add(subscriber)\n\n def unsubscribe(self, subscriber):\n """Remove subscriber added previously.\n When there are no more subscribers the listener is stopped.\n """\n with self.__lock:\n self.__subscriptions.remove(subscriber)\n if not self.__subscriptions:\n self.disconnect()\n\n def disconnect(self):\n """Disconnect from the twitter stream and remove from the cache of listeners."""\n with self.__lock:\n if self.__connected:\n _log.info("Disconnecting twitter stream for [%s]" % ", ".join(self.__phrases))\n self.__listeners.pop(self.__phrases)\n self.__stream.disconnect()\n self.__connected = False\n\n @classmethod\n def disconnect_all(cls):\n """Disconnect all listeners."""\n with cls.__lock:\n for listener in list(cls.__listeners.values()):\n listener.disconnect()\n\n def on_data(self, data):\n data = json.loads(data)\n with self.__lock:\n self.__tweets.insert(0, data)\n self.__tweets = self.__tweets[:self.__max_size]\n for subscriber in self.__subscriptions:\n try:\n subscriber.on_data(data)\n except:\n _log.error("Error calling subscriber", exc_info=True)\n return True\n\n def on_error(self, status):\n with self.__lock:\n for subscriber in self.__subscriptions:\n try:\n subscriber.on_error(status)\n except:\n _log.error("Error calling subscriber", exc_info=True)\n\nif __name__ == '__main__':\n import time\n logging.basicConfig(level=logging.INFO)\n\n class TestSubscriber(object):\n """simple subscriber that just prints tweets as they arrive"""\n\n def on_error(self, status):\n print("Error: %s" % status)\n\n def on_data(self, data):\n print(data.get("text"))\n\n subscriber = TestSubscriber()\n listener = TwitterListener.get_listener(["python", "excel", "pyxll"], subscriber)\n\n # listen for 60 seconds then stop\n time.sleep(60)\n listener.unsubscribe(subscriber)\n<\/pre><\/pre>\n\n\n\n\npython twitterxl.py\n\nINFO:__main__:Creating listener for python, excel, pyxll\nINFO:requests.packages.urllib3.connectionpool:Starting new HTTPS connection (1): stream.twitter.com\nLinuxtoday Make a visual novel with Python: Linux User &amp;amp;amp;amp;amp; Developer: Bridge the gap between books...\nHow to create drop down list in excel https:\/\/t.co\/Ii2hKRlRBe...\nRT @papisdotio: Flying dron with Python @theglamp #PAPIsConnect https:\/\/t.co\/zzPNSFb66e...\nRT @saaid230: The reason I work hard and try to excel at everything I do so one day I can take care ...\nRT @javacodegeeks: I'm reading 10 Awesome #Python Tutorials to Kick-Start my Web #Programming https:...\nINFO:__main__:Disconnecting twitter stream for\n\n[python 1="excel," 2="pyxll" language=","][\/python]\n\nProcess finished with exit code 0\n<\/pre><\/pre>\n\n\n\n
Getting the Data into Excel<\/h2>\n\n\n\n
RTD<\/code>. When a function decorated with the xl_func<\/code> decorator returns an RTD instance, the value of the calling cell will be the value<\/code> property of the RTD instance. If the value property of the returned RTD instance later changes, the cell value changes.<\/p>\n\n\n\n\nfrom pyxll import RTD\n\nclass TwitterRTD(RTD):\n """Twitter RTD class that notifies Excel whenever a new tweet is received."""\n\n def __init__(self, phrases):\n # call super class __init__ with an initial value\n super(TwitterRTD, self).__init__(value="Waiting for tweets...")\n\n # get the TwitterListener and subscribe to it\n self.__listener = TwitterListener.get_listener(phrases, self)\n\n def disconnect(self):\n # overridden from RTD base class. Called when Excel no longer\n # needs the RTD object (for example, when the cell formula\n # is changed.\n self.__listener.unsubscribe(self)\n\n def on_error(self, status):\n self.value = "#ERROR %s" % status\n\n def on_data(self, data):\n self.value = data.get("text")\n<\/pre><\/pre>\n\n\n\n\nfrom pyxll import xl_func\n\n@xl_func("string[] phrases: rtd")\ndef twitter_listen(phrases):\n """Listen for tweets containing certain phrases"""\n # flatten the 2d list of lists into a single list of phrases\n phrases = [str(x) for x in itertools.chain(*phrases) if x]\n assert len(phrases) &amp;amp;amp;amp;gt; 0, "At least one phrase is required"\n\n # return our TwitterRTD object that will update when a tweet is received\n return TwitterRTD(phrases)\n<\/pre><\/pre>\n\n\n\n![\"screengrab1\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2016\/03\/screengrab1.gif\")
<\/a><\/figure>\n\n\n\nA More Complete Twitter Feed<\/h2>\n\n\n\n
\nclass TwitterRTD(RTD):\n """Twitter RTD class that notifies Excel whenever a new tweet is received."""\n\n def __init__(self, phrases, row=0, key="text"):\n super(TwitterRTD, self).__init__(value="Waiting for tweets...")\n self.__listener = TwitterListener.get_listener(phrases, self)\n self.__row = row\n self.__key = key\n\n def disconnect(self):\n self.__listener.unsubscribe(self)\n\n def on_error(self, status):\n self.value = "#ERROR %s" % status\n\n def on_data(self, data):\n # if there are no tweets for this row return an empty string\n tweets = self.__listener.tweets\n if len(tweets) &amp;amp;amp;amp;lt; self.__row or not tweets[self.__row]:\n self.value = ""\n return\n\n # get the value from the tweets\n value = tweets[self.__row]\n for key in self.__key.split("\/"):\n if not isinstance(value, dict):\n value = ""\n break\n value = value.get(key, {})\n\n # set the value back in Excel\n self.value = str(value)\n<\/pre><\/pre>\n\n\n\n\n@xl_func("string[] phrases, int row, string key: rtd")\ndef twitter_listen(phrases, row=0, key="text"):\n """Listen for tweets containing certain phrases"""\n # flatten the 2d list of lists into a single list of phrases\n phrases = [str(x) for x in itertools.chain(*phrases) if x]\n assert len(phrases) &amp;amp;amp;amp;gt; 0, "At least one phrase is required"\n\n # return our TwitterRTD object that will update when a tweet is received\n return TwitterRTD(phrases, row, key)\n<\/pre><\/pre>\n\n\n\n![\"screengrab3\"](\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2016\/03\/screengrab3.gif\")
<\/a><\/figure>\n\n\n\n\nfrom pyxll import xl_on_close\n\n@xl_on_close\ndef disconnect_all_listeners():\n TwitterListener.disconnect_all()\n<\/pre><\/pre>\n\n\n\n
Other Use Cases<\/h2>\n\n\n\n