Outlook Linked Tables and a Flutter on The Stock Market

For Access 2003 and Access 2007

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.

Linking to an Outlook Folder

Figure 1 – Linking to an Outlook Folder

 

Click for larger image
Choose to Link to the Outlook folder


Figure 2 – Choose to Link to the Outlook folder

 

Choose which Outlook folder you want to link to

Figure 3 – Choose which Outlook folder you want to link to

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

The linked email table with subject structure highlighted. Note some information hidden

Figure 4 – The linked email table with subject structure highlighted. Note some information hidden

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.

Using the Immediate Window to construct the fields for my query

Figure 5 – Using the Immediate Window to construct the fields for my query

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.

Adding one of the Immediate Window functions into a query

Figure 6 – Adding one of the Immediate Window functions into a query

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.

The append query that I use to move the data to a table

Figure 7 – The append query that I use to move the data to a table

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

Garry Robinson
Garry Robinson
Garry Robinson was the editor of www.vb123.com and the Access Unlimited Newsletter. He wrote a book on Microsoft Access Protection and Security and has written many articles for the Smart Access Magazine. Amongst Garry's online contributions is an Access 2007 Security paper for MSDN at http://msdn2.microsoft.com/en-us/library/bb421308.aspx. When Garry isn't working, he likes playing golf, snorkeling and being dragged along to kids soccer and kids basketball and kids golf and kids surf lifesaving and the second son hasn't even started sports yet!

Latest Articles