Correlating dates, times, stock movements
June 17, 2010 6:13 PM Subscribe
I have lists, in Excel spreadsheets, that contain perhaps 500 rows of data in three columns: Date, Time and Stock Ticker Symbol. I need a method to correlate those three things with unusual stock activity.
In other words, if column 1 is March 10, 2010, and column 2 is 10 a.m., and column three is INTC, I need to know if something unusual happened - high volume or big price movement - for INTC around 10 a.m. March 10.
Anyone know a program that'll do this?
Given access to bloomberg, downloading price and volume say ten minutes on either side of your time is pretty straight forward. How serious are you about defining unusual? This would sort of a pain to do what a finance professor would consider correctly.
posted by shothotbot at 7:48 PM on June 17, 2010
posted by shothotbot at 7:48 PM on June 17, 2010
I need to know if something unusual happened - high volume or big price movement - for INTC around 10 a.m.
Unusual how? A price move greater than the mean? The weighted mean? The median? It's not really clear how you're defining "unusual" but likely there is an Excel function that can extract the data you're looking for.
But you need to define what you mean by unusual before anyone here can offer any guidance.
posted by dfriedman at 9:38 PM on June 17, 2010
Unusual how? A price move greater than the mean? The weighted mean? The median? It's not really clear how you're defining "unusual" but likely there is an Excel function that can extract the data you're looking for.
But you need to define what you mean by unusual before anyone here can offer any guidance.
posted by dfriedman at 9:38 PM on June 17, 2010
Your question glosses over the all-important fourth column of data - the stock activity (change in price or NAV, presumably) at date A, time B, and stock C.
Of course, you really only have 3 columns: time A+B, stock C, and activity D.
Are you asking how to find D? How to pick which D's are "unusual"? Something else?
Please restate your question so we can help.
posted by IAmBroom at 10:37 PM on June 17, 2010
Of course, you really only have 3 columns: time A+B, stock C, and activity D.
Are you asking how to find D? How to pick which D's are "unusual"? Something else?
Please restate your question so we can help.
posted by IAmBroom at 10:37 PM on June 17, 2010
sounds like you'll have to roll this yourself. as IAmBroom said, column D is the winner. let alone how you choose to define unusual events you'll need access to historical intraday stock prices (since in your OP you specify the precision down to the nearest hour), which will inevitably cost money. consider these sources:
posted by asymptotic at 8:15 AM on June 18, 2010
- IQfeed, $50 + $60/month, Windows-only
- Data Bull, unsure about price, Windows-only
- Equity Market Data, $15/month?, cross-platform (since it's an Eclipse plugin?)
- Whoever you invest through may provide historical intra-day price data.
posted by asymptotic at 8:15 AM on June 18, 2010
[Lots of stuff about Tableau]
Just to be clear, Tableau looks cool, but its strictly bring your own data, right? The OP doesn't have the data (and maybe needs to decide what data he needs) in addition to a way of examining "unusual stock activity."
posted by shothotbot at 5:44 PM on June 18, 2010
Just to be clear, Tableau looks cool, but its strictly bring your own data, right? The OP doesn't have the data (and maybe needs to decide what data he needs) in addition to a way of examining "unusual stock activity."
posted by shothotbot at 5:44 PM on June 18, 2010
From the OP's post:
I have lists, in Excel spreadsheets, that contain perhaps 500 rows of data in three columns...
Sounds like he does have the data, unless I am missing something?
posted by meepmeow at 7:42 PM on June 18, 2010
I have lists, in Excel spreadsheets, that contain perhaps 500 rows of data in three columns...
Sounds like he does have the data, unless I am missing something?
posted by meepmeow at 7:42 PM on June 18, 2010
His three columns are the date, the time and the ticker.
posted by shothotbot at 6:56 AM on June 19, 2010
posted by shothotbot at 6:56 AM on June 19, 2010
Duh! Please can an admin delete my first post in this thread (reading FAQ now to see if I can do that).
posted by meepmeow at 9:31 AM on June 19, 2010
posted by meepmeow at 9:31 AM on June 19, 2010
Response by poster: Thanks for your thoughts, everyone. I'm flexible in my definition of "unusual activity" - if I could identify big changes in price OR volume, that'd be great. Both would be better.
And the definition of "big" changes is up for grabs, too. I suspect if you can teach me to spot, say, a 3% price change in 10 minutes, or a spike in volume (by any reasonable definition), I could figure out a way to change the technique to apply to, say, a 4% change. In other words, I'm perfectly happy to accept your definition of "unusual."
And even if we can't get it more fine-grained than the activity in one trading day, that'd be a big help, too.
The key here is to avoid looking up 500 of these on Yahoo. I want to be able to do this again and again with different lists.
Is that clearer?
posted by Flunoid at 8:48 PM on June 19, 2010
And the definition of "big" changes is up for grabs, too. I suspect if you can teach me to spot, say, a 3% price change in 10 minutes, or a spike in volume (by any reasonable definition), I could figure out a way to change the technique to apply to, say, a 4% change. In other words, I'm perfectly happy to accept your definition of "unusual."
And even if we can't get it more fine-grained than the activity in one trading day, that'd be a big help, too.
The key here is to avoid looking up 500 of these on Yahoo. I want to be able to do this again and again with different lists.
Is that clearer?
posted by Flunoid at 8:48 PM on June 19, 2010
Response by poster: Just to be clear: any information I could get about these would move me a considerable distance to my goal. Give me an approach that flags, say, all instances where a stock rose 5% in a day, or maybe quadrupled average trading volume, and that'd be a big step in the right direction. A single trading day is about as coarse-grained as I can get with the data - a whole week wouldn't do me any good.
posted by Flunoid at 8:52 PM on June 19, 2010
posted by Flunoid at 8:52 PM on June 19, 2010
« Older The best part of waking up is ... feta? | My In-laws are insane, but do they have to be... Newer »
This thread is closed to new comments.
posted by Frank Grimes at 6:49 PM on June 17, 2010