Putting together multiple queries on same sheet get it now
So far we've been considering Rest queries to be discrete. In this section we are going to build a complete application that regularly collects data from multiple places and reports on it.
Try it out first ?
These are enhancements to Data Manipulation Classes so they have been included in cDataSet.xlsm, which is downloadable from Download Complete Projects.
Note: Tweetsentiments.com has withdrawn free access to it's API. This example has been removed from the example workbook for now.
In this example we are going to take a look at how to query tweetsentiments, topsy and Google Finance on a scheduled basis, populate a spreadsheet with the results, and track changes over time.
Starting from a stock ticker, we can look up the current stock price on Google Finance, use Tweetsentiments to see the sentiment at a point in time, and use topsy to count the moving average of tweets for a given day at a given time. These things together might (or might not) show a correlation between sentiment and stock price.
Here is the combined table we are going to create at regular intervals. For this I've picked some random stocks to track. It's a little hard to read, but I'll take you through the details in the following sections.
The first part of the process is to fill in a few pieces of data from a rest query to Google Finance, using the ticker as input. The current stock price is in the column marked 'l'.
Next, we'll take the current tweetsentiment using the ticker for each stock. It is standard practice to use $ticker on twitter when making stock price tweets. It may be that it's worth querying on the company name as well to look for general sentiments about a company, but for this illustrative example, let's use $ticker.
We can use topsy to find out the volume of tweets on a particular, topic - in this case $ticker for each stock being tracked.
We'll add a timestamp to each row, so that we can analyze these over time later.
Since we want to track this over time, we'll automatically run this every now and again, and build up a backlog of data to create a chart over time, and refresh that with every update.
There is nothing new here that hasn't been covered elsewhere in the Rest to Excel library, but here is how to put all this together.
Run all the queries, move the collected data to a growing repository and update any charts.
Public Sub testandbucket()
Execute the whole process and schedule the next one.
Public Sub scheduledbucket()
Set up the next one to run in 20 minutes
Public Sub Schedulethebucket()
Dim pn As Date
pn = Now + TimeSerial(0, 20, 0)
Application.OnTime pn, "scheduledbucket"
Do all the queries, and add a timestamp. The definition of the queries have all been added to the Rest to Excel library. Note that there is not much work to do here since all the nasty bits are mainly handled by the Data Manipulation Classes
Public Function testCombineFinanceSentiment() As cRest
Dim dr As cDataRow, cc As cCell, cr As cRest, t As Date
'get the ticker data
generalReport restQuery _
("investbysentiments", "google finance", , "ticker", , , , , False) _
'add the tweet sentiments
Set cr = generalReport(restQuery _
("investbysentiments", "tweetsentiment topics", , "topic", , , , , False) _
'add the topsy counts
Set cr = generalReport(restQuery _
("investbysentiments", "topsy count", , "topic", , , , , False) _
t = Now()
For Each cc In .Rows
cc.Value = t
After each scheduled query set, the collected data is moved to a repository for later analysis. Again, the Data Manipulation Classes do most of the dirty work.
Private Sub moveToBucket(Optional bCleanAfter As Boolean = True)
Dim ds As cDataSet, dsb As cDataSet, w As Range
Set ds = New cDataSet
' get the currently populated stuff
.populateData wholeSheet("investbysentiments"), , , , , , True
' get the current bucket
Set dsb = New cDataSet
.populateData wholeSheet("investbysentimentsbucket"), , , , , , True
' assume columns are all the same and just to a big commit
Set w = .where
If w Is Nothing Then
Set w = .HeadingRow.where.Offset(1)
Set w = .where.Offset(.Rows.Count)
ds.bigCommit w.Resize(1, 1), , , , , , False
If bCleanAfter Then
Set ds = Nothing
Set dsb = Nothing
Refreshing any pivot caches will update any associated pivots and charts
Dim pc As PivotCache
Dim ws As Worksheet
For Each pc In .PivotCaches
Since we want this to be automatic, we will use a named Dynamic range for the reporting pivot table and chart, defined as follows.
=OFFSET(InvestBySentimentsBucket!$A$1,0,0, COUNTA(InvestBySentimentsBucket!$A:$A), COUNTA(InvestBySentimentsBucket!$1:$1))
The tracking chart looks like this