October 30, 2015 at 3:06 pm
Hi all,
I was hoping that someone could provide some advice regarding the following dilemma; I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalisation...
Date CompA CompB
01/01/11 100 5
02/01/11 102 4
Share Price....
Date CompA CompB
01/01/11 100 100
02/01/11 101 99
Event Data...
Date Company
01/01/11 CompA
02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
Does anyone have any ideas of the best way to do this? I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance:
SELECT Event.Date, Event.Company
FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
Does anyone have any ideas that might help? (sorry for my bad SQL'ness)
Thanks,
October 31, 2015 at 10:46 pm
You need to modify the table Event Data , Add MC.CompA values if you want to filter depending on CompA values or if you need to select by CompB values need to add that field in ED table.
Something like query
select MC.*,SP.*,Ed.* from MarketCap MC
inner Join SharePrice SP
ON MC.CompA=SP.CompA
Inner Join EventData ED
On MC.CompA=ED.Range(CompAvalue)='100'
Or MC.CompB/SP.CompB=ED.Range(CompBValue)='5'
@JayMunnangi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply