Recently, I came across an excellent online resource that I can’t resist sharing with my readers. The resource is an online library of functions and APIs that you can use easily with Excel and Google spreadsheets to perform tasks such as k-means clustering, data visualization, and sentiment extraction etc. The resource is available at https://www.blockspring.com.
In this post, I will show you how to build a sentiment monitor for your favorite stock using only two formulas in your spreadsheet using Blockspring. To get started, the first thing you need is to sign up at Blockspring and install the plug-in for Excel. It works for Excel 2010 and Excel 2013. Once you have installed the plug-in, it will appear as a tab on Excel Ribbon. Selecting the tab will open the Blockspring panel from where you can log-in to your Blockspring account to make use of a large number of functions available at Blockspring.
Clicking Console will open a sidebar window at the right side of the spreadsheet where you can browse through the different functions that you can use for your work.
As you scroll down the console, you will see the different functions that are for you to use. For our project, we will filter the functions for news.
Looking at filtered functions, let us select “Get Recent News for Stock Ticker” function. The Console sidebar will now show you a brief description of this function. Basically, this function returns a specified number of news stories for a given stock ticker by inserting the following formula in a cell:
=BLOCKSPRING(“get-recent-news-for-stock-ticker”, “ticker”, “FB”, “num_items”, 5)
In above, we are requesting five stories about Facebook stock whose ticker symbol is FB. Entering the above formula in cell A1 immediately pulls in stories related to Facebook with their titles, URLs, and date and time of publication.
What we will do next is to use another Blockspring function that computes sentiment of a story given its URL. To locate this function, we go back to Console and filter functions provided by Alchemy-API.
As you browse through Alchemy-API functions, you will notice that there are many functions that give you powerful capabilities, based on natural language processing (NLP) and image processing, to build smart applications. In our case, we select “Sentiment Analysis from URL” function. Before you can use this function, however, you must register with AlchemyAPI and obtain the API key which is free for a certain number of uses.
Once you get your key, make sure to save it in your Blockspring account. Next, we enter the following formula in cell D2 and copy it down to cell D6:
=BLOCKSPRING(“sentiment-analysis-from-url-with-alchemyapi”,”url”,B2)
This will result in sentiment score for each story. In this case, the scores are provided in Blockspring Viewer in Console.
To view the sentiment score for the first story referenced in cell D2, we select cell D2 and click Viewer tab in Console, where we find a negative sentiment score for this story. We can view the scores for other stories similarly by highlighting cells D3:D6. The “Share result” button allows you to share the results with others, if needed.
As the above example shows, Blockspring provides a large number of useful functions and APIs to quickly build smart applications without any programming and relying upon universally understood Excel interface. Let me know if you create an interesting application with Blockspring so that it can be shared with other readers.
[This post also appears on my other blog at excelvbacomputing.wordpress.com
under a different heading]
From Data to Decisions 