Outlook Linked Tables and a Flutter on The Stock Market
June 19, 2009
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.
Click for larger image
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