We’ll add a timestamp to each row, so that we can analyze these over time later.
Public Sub testandbucket() testCombineFinanceSentiment moveToBucket False pivotCacheRefreshAll End Sub </p>
Execute the whole process and schedule the next one.
Public Sub scheduledbucket() testandbucket Schedulethebucket 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 cr.dSet.bigCommit 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) Else Set w = .where.Offset(.Rows.Count) End If ds.bigCommit w.Resize(1, 1), , , , , , False If bCleanAfter Then ds.where.ClearContents 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 pc.Refresh 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))
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines