Combining multiple rest queries

What can you learn here ?
  • Using cRest
  • Multiple sources
  • Combining

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: has withdrawn free access to it's API. This example has been removed from the example workbook for now.

Using tweets and sentiments to compare with stock price.

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. 

The combined table

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. 

Google Finance

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'.

Tweet sentiments

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. 

Adding a timestamp

We'll add a timestamp to each row, so that we can analyze these over time later.

Scheduling and consolidation

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.

The Code

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()
    moveToBucket False
End Sub

Execute the whole process and schedule the next one.
Public Sub scheduledbucket()
End Sub

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"
End Sub

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) _
        , False
    'add the tweet sentiments
    Set cr = generalReport(restQuery _
        ("investbysentiments", "tweetsentiment topics", , "topic", , , , , False) _
        , False)

    'add the topsy counts
    Set cr = generalReport(restQuery _
        ("investbysentiments", "topsy count", , "topic", , , , , False) _
        , False)
    t = Now()
    With cr.dSet.Column("timestamp")
         For Each cc In .Rows
            cc.Value = t
         Next cc
    End With
End Function

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
    With ds
        .populateData wholeSheet("investbysentiments"), , , , , , True
    End With
    ' get the current bucket
    Set dsb = New cDataSet
    With dsb
        .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)
        End If
        ds.bigCommit w.Resize(1, 1), , , , , , False
        If bCleanAfter Then
        End If
    End With
    Set ds = Nothing
    Set dsb = Nothing
End Sub

Refreshing any pivot caches will update any associated pivots and charts
Sub pivotCacheRefreshAll()

    Dim pc As PivotCache
    Dim ws As Worksheet

    With ActiveWorkbook
        For Each pc In .PivotCaches
        Next pc
    End With

End Sub

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))

Tracking chart

The tracking chart looks like this