Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    58

    Unanswered: Market Cap calculations.

    Hey all, back with another little problem that I've just encountered, it might be very easily solved, here goes:

    I have got a Stock Market Database with table structure attached. I have calculated the Market Cap using a simple straight up multiplication by picking the last possible day and then multiplying the 'SharePrice' with 'NOSH'(Number of Shares).

    The problem comes when you realize that in the UK, the SharePrice is always in pence whereass in all the other places it would be in dollars(instead of cents) etc. So I would have to divide the UK value by 100 to get the right Market Cap. I have the following code:

    Code:
    SELECT Company_Information.CompanyName, [Last Day].StockCode, [Last Day].Dates, [Last Day].SharePrice, Company_Information.NOSH, [Company_Information]![NOSH]*[Last Day]![SharePrice] AS MarketCap, Company_Information.CurrencyCode
    FROM [Last Day] INNER JOIN Company_Information ON [Last Day].StockCode = Company_Information.StockCode
    [Last Day] is the query I've made to pick the last day, and then this second query afterwards to do any calculations. I was thinking some sort of WHERE clause in which if CurrencyCode=GBP then it would /100, but im not sure what the right Syntax for this would be.
    Attached Thumbnails Attached Thumbnails Database relationship.JPG  
    Last edited by md85; 07-19-10 at 08:58.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    might be better to use an iif statement

    another approach would be to apply the scaling factor in a currency code table and pull that scaling factor from the currecny table

    however not all UK shares are priced in pence, so are priced in pounds......
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2010
    Posts
    58
    True, but all the UK shares that are going to be in this particular database are priced in pence, it would make things much harder if they were not.

    I'm trying out the IIF statement now, & how do I apply a scaling factor? Any useful links I can look up? Google isn't always the best option. What in your opinion is the best solution.

    Also another thing I would like to know while we mentioned the Historical_Currency table, whats the best way to convert all the Market Caps in dollars; Crosstab query? Because even if for the Daily market update I would only need todays values, but for example if I wanted to quickly get the values for two companies, one based in Canada, another based in the UK, for the last two years in Dollars, to get a graph ready or anything of that sort, what would be the best possible solution?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the help system to fidn the syntax of the IIF

    all the UK shares that are going to be in this particular database are priced in pence
    get that signed of as a requirement
    document it in your code, because someday someone is going to break that

    yourscaling factor is going to be multipying by 0.01 to convert pence to pounds if the currency code is GBP
    in essence your IIF statement will be soemthign like

    iif(CurrencyCode="GBP", 0.01,1) as scalingfactor
    or
    iif(CurrencyCode="GBP", price*quantity*0.01,price*quantity) as holdingvalue

    select my, comma, separated, columns, iif(CurrencyCode="GBP", price*quantity*0.01,price*quantity) as holdingvalue from mytable
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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