Outlook Linked Tables and a Flutter on The Stock Market

In this article, I
will demonstrate how you can get a series of email subject line headers into a
database table. To illustrate that I will use the emails from the most popular
Internet Share Trading site in Australia. While you may not be a share trader, you may well get many
emails from some organization that has important information in the subject
line. Apart from Microsoft Access 2003 or 2007, you will need a copy of
Microsoft Outlook. In this article, I will demonstrate Outlook 2007.

Getting Subject Lines into Your Database

To make this exercise
work, you are going to need all the emails that you want to view in the
database organized into the one Outlook folder. As you are going to do this
systematically, you will need to use an Outlook Rule to move the email from the
Inbox into this folder.

So let’s get started.
First set up one or more emails in a standalone Outlook folder. Now start the
Outlook Linking wizard as shown in Figures 1, 2, 3. Then to complete the task,
give the Linked Table a Name.

In Figure 4, you will
see what the subject line field and everything else in the linked table looks
like.

Once you are happy
that the Table Linking works, your next job is to create an Inbox rule to move
all new emails into this folder as they arrive. For more on Outlook rules, see
my article on How
To Read A PayPal Notification Email. 

Reading the Subject Line

After moving all of
the emails into the new folder and creating the Inbox rule, have a look at the
sample Subject line below.

Trade Confirmation: BUY 530 WBC @ 22.640000 on Account 60991 (C28856498)

If you were to look
at the linked Outlook table, you would find that all the entries in Subject
field are very consistent.  This means we can create an algorithm to break
up the Subject line into the different elements like BUY or SELL, amount of
shares, price and Contract note ID (see Figure 4). Let’s now find out how we
can break up those fields.

To manage that break-up
we need to create a query that breaks the Subject line into different fields.
To illustrate how I prototyped that breakdown, have a look a Figure 5. In this sample, I have done all the
prototyping using the VBA intermediate Window. You can bring up the Immediate
Window pressing the Ctrl+G keys together. In all the samples I have used the GetWord
function (see section below) to get the different information in the subject
line. The most technical equation demonstrated in Figure 5 is where I have used
the Mid and Len string functions to remove the brackets from the Contract note
Identifier.

Once you have done
this prototyping, save the successful commands to NotePad just for safekeeping
and then open a new query. Add the Linked Outlook table (InboxShares in my
case) to the Query window and you are ready to dissect the Subject line into
separate fields.

Note: When you
transfer from the Immediate Window into the query window, change the word StrSubj
to Subject as shown in Figure 6.

Stock/Shares Query Listing

Once you have a grasp
of extracting the Subject line matter into their different parts, you would put
together a full package of fields in a select query. If you only ever want a
read only view of the Outlook data, this will be what you will use as a query
view or even as the source for an Access report.

What follows is the
full query that I use to extract the Share information from Commsec (the Aussie
share trading account). The fields mentioned in this query in order are BUY or
SELL code, Contract ID, Number of Shares, Stock/Share Code, Price paid for the
stock, the amount Commsec charges for a Share Trade, the (Cost of the Trade –
Charge), Total Cost (negative for Sell), and the Day of transaction.

SELECT Getword([subject],3) AS BuySell,

 Mid(Getword([subject],11),2,Len(getword([subject],11))-2) AS ContractID,

 Getword([subject],4) AS NumShares, IIf([BuySell]=”Buy”,[numShares],-[numShares]) AS NShares,

 Getword([subject],5) AS stockCode,

 Getword([subject],7) AS Price,

 19.95 AS Charge,

 ([NumShares]*[Price])+IIf([BuySell]=”Buy”,[Charge],-[Charge]) AS TCost,

 IIf([BuySell]=”Buy”,[tcost],-[tcost]) AS TotalCost,

 Format([Received],”yyyy-mmm-dd”) AS [Day]

FROM InboxShares

ORDER BY Getword([subject],5), Format([Received],”yyyy-mmm-dd”);

Load the Data into a Table BUT
Get The Index Right

In my case, I want to
enter other information as well as view the fields constructed in the Stock
query. To achieve this I have to append that Stock information to a table.

Loading this query
into a table in the database requires you have a primary key in the table to
stop duplicate rows from being loaded into the table. In the case of this Stock
Information loading system, I have set the Contract number as the Primary
key.  Having a unique Primary key in the table means that you can leave
the emails in the folder for ever and just keep appending them knowing that the
information will never be loaded twice.

To update my
database, I will load that information into the table whenever I open the
database using this simple piece of code.

Private Sub Form_Open(Cancel As Integer)

  MsgBox “Adding new shares”

  DoCmd.SetWarnings False

  DoCmd.OpenQuery “AddShares”

  DoCmd.SetWarnings False

End Sub

The GetWord Function

The important
function that I have used in this article is the GetWord function.  This
was published by Microsoft in the free code samples that came with the Access
97 package.  It’s a pretty useful routine.

Function GetWord(S, Indx As Integer)

‘

‘ Extracts a word in text where words are separated by 1 or more spaces.

‘

Dim i As Integer, WC As Integer, Count As Integer

Dim SPos As Integer, EPos As Integer, OnASpace As Integer

WC = CountWords(S)

If Indx < 1 Or Indx > WC Then

  GetWord = Null

  Exit Function

End If

Count = 0

OnASpace = True

For i = 1 To Len(S)

  If Mid(S, i, 1) = ” ” Then

    OnASpace = True

  Else

    If OnASpace Then

      OnASpace = False

      Count = Count + 1

      If Count = Indx Then

        SPos = i

        Exit For

      End If

    End If

  End If

Next i

EPos = InStr(SPos, S, ” “) – 1
If EPos <= 0 Then EPos = Len(S)
GetWord = Mid(S, SPos, EPos – SPos + 1)
End Function

The Stock Exchange Sample

To see how the system
works, download the sample database.  Inside the zip files, you will find three
emails that you can drag and drop into your NEW shares folder in
Outlook.   You will also find in the database the query that appends
the Outlook data into Access. 

If you are a share
trader, you will find that there is a field call the BuyTag.  What I do
with this is match the BUY share records with the SELL share records when the
total number of shares traded in this group sums to zero. In that BuyTag field,
I enter the Contract Id of the Buy transaction. This way I can work out the
total loss or gain on all the trades in that group. Unfortunately, it hasn’t
been all gains in the last year.  Good luck with your Outlook linked
tables and if you are a share trader, always keep a little bit aside because
what goes up, can come down. 

Download the files for this article.

»
See All Articles by Columnist Garry Robinson