Serverless.

The best way for me to learn something is to use it, I’m hands-on. After being barraged by articles and hype around Azure Functions, I decided to try it out. But, I’m not a developer. All the new stuff that the cool kids are playing with today (IoT, Serverless, Bots, AI, etc.) requires real dev skills but that’s just not what I do. Sorry, not sorry. So here we go with PowerShell and Azure Functions. Warning: support for PowerShell is “experimental” and I found that I had to do a lot of workarounds.

The project

I’m going to use Azure Functions to generate a trade log. I’ve used all kinds of stock brokers (Schwab, Scottrade, Fidelity, etc.) but no platform gives you the flexibility and cost of Interactive Brokers ($IBKR). Most of my trades cost $1.00 but you get what you pay for. $IBKR doesn’t have a nice, rich, web interface that shows your trade history. Instead you have to run  your own reports and luckily there’s an API for it. My function is going to create a “up to date” log of the trades that have been executed (a lot of my trading is automated so I might not know when something has been bought or sold).

I’m going to focus on Functions and specifically this project. There’s a lot of great documentation on the Interactive Broker API as well as Azure Functions on their respective official documentation pages.

Setting up

So the first thing I’m going to do is create a new Azure Functions app using PowerShell and starting with the Timer trigger. Let’s call this “GetIBTrades2Blob.” We’re going to go right into the “Integrate” section and configure the following:

Triggers: Timer Trigger called “Daily0200ZZ” that has a schedule of “0 0 2 ***” That means, it will run at ANY day of week, month, or day at 2 hrs, 0 minutes, and 0 seconds. That’s 2:00:00 daily (Zulu time), which is 8 PM Eastern.

Inputs: Azure Blob Storage. I have a parameter name “inputBlob” with an explicit path, “trades/tradelog.csv” and a Storage account connection. Azure Functions sets this up nicely just by going through the portal UI. One note is that I struggled a lot with the path. It seems PowerShell doesn’t really support variables here (the default is {file}). I hope this will be fixed at some point but it may just be a limitation of the language.

Output: Azure Blob Storage. This is setup almost exactly the same way and the input, we’re just going to use a different parameter name but the same path and connection.

All of that just writes your functions.json file for you. Next, we need to write the code in run.ps1.

Code

The code isn’t really relevant for this blog post except for a few key pieces that make everything work. The code might as well just be something like

“hello world” | Out-File –FilePath $outputBlob

But, let’s see what we’re dealing with for Interactive Brokers. First, we need two parameters, a Token Code and a QueryID (see this link). This can be retrieved from your $IBKR account. For the QueryID, I created a new one (that is, a new Flex Query) that just gets my trades for the current day (see this link).

Now that we have those, we can get the trade confirmation report for today using Invoke-WebRequest and doing some parsing.

$token = "000000000000000000000000"
$q = "123456"

$request = "https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest?t=$token&q=$q&v=3"

$response = Invoke-WebRequest $request -UseBasicParsing

[xml]$xml = $response.Content
[string]$refCode = $xml.ChildNodes.ReferenceCode
[string]$flexUrl = $xml.ChildNodes.Url

$reqData = $flexUrl + "?q=$refCode&t=$token&v=3"
$responseData = Invoke-WebRequest $reqData -UseBasicParsing
   
$content = $responseData.Content

What will be returned here is an array containing the trades returned by the query, for example:

Date/Time,Symbol,Quantity,Price,Amount,Commission,OrderType,TradeDate
20180518;134156,XYZ,-100,5.2,-94,-1,LMT,20180518

Great! But now I need to add this trade to the existing ones (from yesterday, the day before, and before that, etc.).

This is a learning exercise so I decided to get the current days trades. I realize that there is a “Month to date” Flex Query available in $IBKR. One of the things that I learned through this exercise is that appending data is not easy (or possible) in Azure Functions using PowerShell. For C#, many examples exist using Append Blob (some information here on that). So, with that said, we have to do a workaround. The workaround will be to get the existing tradelog.csv and that’s exactly why we added the Azure Blob Storage as an input.

#Remove header information from $content since it will already be there in the file we’re getting
$cleanContent = $content | Select-Object -Skip 1

#Get the current file from blob storage
$inputArray = Get-Content -Path $inputBlob

foreach ($line in $cleanContent)
     {
         $inputArray += $line
     }


Out-File -Encoding ASCII -FilePath $outputBlob -inputObject $inputArray

This is pretty simple. First we remove the heading. Then we loop through each line in the returned results for today’s trade since there could have been more than one trade. The last line is where we store the full text (including the original input) back to Azure Blob Storage. Actually, by adding additional Outputs, you can save the file in many places. For my purposes, I also have the file going to my OneDrive.

In my example, I was working with text. Working with binaries or media would be much harder because of some content type issues, unless you use C# or another supported language. A big thanks to jschmitter, who patiently assisted me working all of this out.