1. Registered User
Join Date
Jun 2010
Posts
58

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.
Last edited by md85; 07-19-10 at 08:58.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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......

3. Registered User
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

#### Posting Permissions

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