Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Los Angeles, California, USA
    Thanked 0 Times in 0 Posts

    Daily Percentage (Access 2000)

    Need help on this one. I am trying to build an Access database that calculates the percentage change of a stock price on a daily basis. The database must be able to give me a warning if the change is greater than 10% but less than 15% or if the change is greater than or equal to 15% per day and per stock. Moreover, the database must be able to count the number of events the stock experienced a 10% or 15% percent change from a five business day stream, monthly, quarterly, 6-month, and 1 year period. I have been doing this in a "crude" excel work sheet but after six month, I noticed that I am running out of columns. Any help would certainly be appreciated.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Vancouver, Br. Columbia, Canada
    Thanked 0 Times in 0 Posts

    Re: Daily Percentage (Access 2000)

    I don't have the complete solution, but hopefully this will get you started in the right direction. This is off the top of my head, based on some work with a similar concept on a different project.

    You should start with a normalized data table of stock ID's, stock prices, and dates. Unlike doing it with Excel, you essentially put all stocks into the same three columns -- you will never run out of columns with this approach. With this structure, it is easy to get the price of any stock on any day, but a problem arises when trying to calculate the difference between dates so you can calculate a percentage change. Easy to do in Excel, tough in Access.

    The key is to make a query that joins the table to itself. Drag two copies of the table onto the query grid. Right-click on the tables, view their properties, and change the alias to "Before" and "After", or something similar. Join them by both StockID and by Date. Add these fields to the grid: StockID, date, price from "Before" table, and price from the "After" table. Run the query to confirm that it is OK so far. Big deal.....

    Now view the SQL of the query. Find the part where it joins the dates from the two queries. It will be something like:
    ... ON Before.StockDate = After.StockDate.

    Now manually change it to:
    ... ON Before.StockDate = After.StockDate - 1

    And rerun the query. You should now be able to see the prices from both dates. Furthermore, they are both accessible to arithmetic operations, so you can calculate the difference like this:

    ... , PriceDiff: After.Price - Before.Price

    You will notice that you can no longer edit the query in the QBE grid because Access is unable to render the non-equal join in the graphical environment. But it works perfectly well using SQL. I usually "cheat" by temporarily removing the "unequal part" from the join expression just so I can modify the query in the graphical environment, then plunk it back in place before running the query.

    Once you get this part working, you should be able to base additional queries on it to do your weekly, monthly, etc summaries. Not sure how you will handle weekend, though... Maybe create a fictitious field with sequention business day numbers?? Dunno...

    Hope this helps.
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts