Introduction
The reports you see in Personal Stock Streamer are simple HTML documents that are generated and formatted by the reporting engine. The built-in reports (capital gains, etc.) are written in C++ and compiled into Personal Stock Streamer, but with the new Personal Stock Streamer 7.0 software it is possible to create custom reports completely in script. For this example, we will create a YTD performance report that shows the difference between the current price of a stock and the price at the beginning of the year.
Assuming you have read the Introduction to Personal Stock Streamer Extensions article, we can jump right into defining the report class.
The Report Handler The report handler class has just one required callback method, GenerateReport(), that will be called by the report generator engine when you select your report from the reports list.
class PriceReportHandler
' create the report
public Function GenerateReport ( Name, Handler, Folder )
end Function
Because multiple reports can have the same handler class, the GenerateReport() method is passed the Name of the requested report so that your code can differentiate between multiple reports. The second argument, the Handler object, is what you will use to get the report parameters and to generate the report. The Folder object is the current folder or portfolio over which the report should be generated.
Since reports are just simple HTML documents, we can start by sending the header to the reporting engine.
' generate report
Handler.WriteReport "<body bgcolor=White>"
Handler.WriteReport "<b>Price Performance Report for " + Handler.FilterPeriod + "</b>"
Handler.WriteReport "<table border=0 cellpadding=0 cellspacing=10>"
Handler.WriteReport "<tr><th><b>Symbol</b></th><th><b>Starting Price</b></th><th><b>Ending Price</b></th><th><b>Gain</b></th><th><b>% Gain</b></th></tr>"
As you can see, this report uses the Handler.FilterPeriod property to show the user what the selected filter period is. The filter period will be the same as what is selected in the Reports tab.
Now we are ready to write the report:
RecurseFolder Handler, Folder
Handler.WriteReport "</table></body>"
Unfortunately we don't get anything for free here. Because portfolios and folders may themselves have nested folders, we must define a RecurseFolder method to go through all of them and generate the report. The last line then writes the end of the table and finishes the report.
So now we have to write the RecurseFolder method, which is the most complicated method in this class.
public Function RecurseFolder ( Handler, Folder )
' process the tickers in the folder
Set Tickers = Folder.Tickers
For i = 1 To Tickers.Count
We start by getting the tickers from the folder and iterating over the collection using the For statement. For each ticker we must retrieve the historical data for the requested date range (ie. the current year) and calculate the difference between the price at the end of the range vs. the price at the beginning of the range.
' get the history data for this year
Tickers.Item(i).SetProp "HistoryStart", Handler.FilterDateStart
Tickers.Item(i).SetProp "HistoryEnd", Handler.FilterDateEnd
Set History = Tickers.Item(i).HistoryData
We are again using properties of the Handler object to get the start and end of the date range requested by the user. Then we retrieve the historical data for that range.
If Not History Is Nothing And History.Count > 0 Then
Here is a little complication. What if the historical data for that range is not available or has not been retrieved yet? We will handle this shortly, but first we handle the case where we do have the data.
BeginValue = History.Item(0).GetProp("Close")
EndValue = History.Item(History.Count - 1).GetProp("Close")
Diff = EndValue - BeginValue
DiffPct = ((EndValue - BeginValue) / BeginValue) * 100 This case is easy. We get the data at the beginning and end of the range and calculate the difference. Then we can write the report:
Handler.WriteReport "<tr><td>" + Tickers.Item(i).GetProp("Symbol") + "</td>"
Handler.WriteReport "<td align=right>" + FormatNumber(EndValue,2) + "</td><td align=right>" + FormatNumber(BeginValue,2) + "</td>"
Handler.WriteReport "<td align=right>" + FormatNumber(Diff,2) + "</td><td align=right>" + FormatNumber(DiffPct,2) + "</td></tr>" This produces a formatted table filled in with all the right values. There is a case that is not handled by this code, and that is what if only partial data is available for the date range? Because Personal Stock Streamer expires the historical data cache each day, this case would not occur and therefore does not need to be handled explicitly.
Now we can handle the case where no historical data is available for the range:
Else
Handler.WriteReport "<tr><td>" + Tickers.Item(i).GetProp("Symbol") + "</td>"
Handler.WriteReport "<td align=right>Waiting for Data</td>"
Tickers.Item(i).DownloadHistoryData Handler.FilterDateStart, Handler.FilterDateEnd
End If
Basically what we are doing here is putting up a "Waiting for Data" sign and requesting historical data for the report range through the Ticker object. This request will happen asynchronously, so somehow we will need to handle it. I will explain this part shortly, but first we need to finish up this method:
Next
' recursively loop through the subfolders
Set Folders = Folder.Folders
For i = 1 To Folders.Count
RecurseFolder Handler, Folders.Item(i)
Next
end Function
This code finishes out the For loop, then recursively calls itself for any nested folders.
Now we get to a little complication. Although the report handler class only has one required method, we need to handle the case where the historical data request was sent to Personal Stock Streamer but the data actually arrives at a later time. Fortunately Personal Stock Streamer provides a way to handle this case by registering interest with the event manager. If I may jump ahead a little bit to show part of the initialization code for this extension:
Set EventManager = Application.GetObject("EventManager")
If Not EventManager Is Nothing Then
EventManager.RegisterHandlerMethod ReportHandler, "OnHistoryUpdated"
End If This is where we hook up our report handler class to the OnHistoryUpdated event that tells us when historical data has arrived for a ticker. Since we register for this event we must now define an OnHistoryUpdated method in the report handler class:
public Function OnHistoryUpdated ( Ticker )
If (Application.GetCurrentView() = "Reports") Then
Set EventManager = Application.GetObject("EventManager")
EventManager.RegisterHandlerMethod me, "OnAppTimer"
End If
End Function
Why aren't we updating the report here? Because OnHistoryUpdated will be called once for each ticker, if you are requesting historical data for multiple tickers at once you could be needlessly updating the report once for each ticker. Instead what we are doing here is registering for yet another event to notify us when the 1-second application timer goes off. This allows us to delay recalculating the report by just a little bit until all of the historical data comes in.
So now we can define the OnAppTimer event handler.
public Function OnAppTimer
EventManager.UnregisterHandlerMethod me, "OnAppTimer"
Set ReportManager = Application.GetObject("ReportManager")
ReportManager.UpdateCurrentReport
End Function
Notice that the first thing we do is to unregister the handler, which makes sure that the handler is not called more than once. Then the last thing is to force an update of the current report, which is done through the report manager object. Note that you can't call the GenerateReport method directly because in the OnAppTimer handler do you not have all of the information and extra objects needed for GenerateReport to work.
So that is the end of the report handler class, and we should not forget to end the class definition.
end Class
Because I already covered part of the initialization with registering for the OnHistoryUpdated event, here is the second part, actually registering the report:
Set ReportHandler = new PriceReportHandler
Set ReportManager = Application.GetObject("ReportManager")
If Not ReportManager Is Nothing Then
ReportManager.Register "Price Performance", ReportHandler
End If
This is probably starting to look very familiar by now. We simply create an instance of the report handler and register it with the report manager.
That is the end of the custom report extension; the full extension code is available here.
So where do you go from here? The sky is the limit. With easy access to your porfolio data and extensive historical data you can now create as many custom reports as you need to help you with your investment decisions.
subscribe to this feed with an RSS reader.