Calling all Excel Wizards
Hi guys,
UPDATE: Guys, I have attached the spreadsheet. I am really close. The problem I am having is when I enter the ticker into concentrationt tracker spreadsheet (A3) it does not automatically update on the demo sheet unless I press enter. Maybe one of you can figure out how to make it work.
I accepted an internship at a bb and I am trying to created an automated spreadsheet for my team. I want my spreadsheet to filter in live market data (15 min delay is okay). The spreadsheet is a concentration tracker so I need to be able to filter in the following market data: current stock price, year high, year low, 50 day moving aveage, and 200 day moving average. I do not have access to paid subscriptions like Thomson one so I need to be able to do this for free.
| Attachment | Size |
|---|---|
| Live CTracker.xls 109 KB | 109 KB |
Sounds like a fun first day, well good luck!
Not my first day, started about one week ago.
http://blogs.office.com/2012/08/14/plug-into-your-data-connecting-excel…
maybe this will help
edit: just read it said to access. so this may not help at all. in that case, good luck.
Send who ever is requesting this an excel spreadsheet with the subscription form to Thompson One in it!
In all seriousness so you don't get fired.
http://beforeitsnews.com/financial-markets/2013/03/excel-tip-5-how-to-p…
I found this faster than it took you to register for this site. But, Welcome!
This is how I do it.
There are plenty of tutorials out there to accomplish what you're looking for. It is a fairly simple VBA code to download the required information, for free, from Yahoo! Finance. But the following links provides a spreadsheet for you.
http://investexcel.net/company-financial-information-web/
How does your firm not have Cap IQ/FactSet etc... there are usually Excel plug-ins that make what you're describing pretty easy.
There is a work around though, see below.
In Excel:
Step 1: Alt T+M+R to start recording macro
Alt+A+FW to do a web query
When the browser window opens up, go to your finance web site of choice (yahoo, google, bloomberg, whichever one is Web Query friendly). Select the datapoints you need. Hit ok.
Alt T+M+R to top recording macro
Step 2: Alt+F11 to open the developer window. Play around with the code the macro recorder just created to make it a sub routine that you can pass a ticker into.
Have a Cache tab in the spreadsheet that is the output for the data the web query loads. Pick out the info points you need, place them into your actual spread sheet and then clear the cache tab and re-runs the web query with the next ticker.
If you don't have any idea what I'm talking about, you're probably not in a position to be automating anything.
it costs a decent amount of money per subscription and my firm won't get it for anyone who doesn't have the series 7, so hey may just be not senior enough.
but to your point, I have a thomson plugin in excel so there's hardly any work to get data to flow through.
Guys, I have attached the spreadsheet. I am really close. The problem I am having is when I enter the ticker into concentrationt tracker spreadsheet (A3) it does not automatically update on the demo sheet unless I press enter. Maybe one of you can figure out how to make it work.
no one can help me out?
Damn, I wish I could help you dude, but I'm not an Excel Wizard, I'm just a level 26 Excel Paladin and my spellcasting abilities are barely any good. If only you were looking for a character with more of an Excel sword-wielding skill set! Sorry I can't help dude.
So I just accepted an offer at GS in IBD for next summer and I suck at excel and do not have a ton of experience with it. Does anyone know of a good online excel program that I could take on my own time before I get there so I don't embarrass myself. Thanks!
Aren't you taking excel/analysis courses in undergrad? My courses taught me more than enough.
Dolorum eligendi est dolorum debitis porro velit. Sint voluptatibus officia veritatis. Omnis in eum aperiam voluptatum molestiae dolores. Ducimus numquam similique iste commodi eos ipsam. Nihil labore sed cum nisi excepturi officia. Debitis quis minima officiis porro fugiat non pariatur.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...