Excel help + stock close prices
Trying to pull stock close prices, specifically on the first trading day of each month, into an Excel worksheet.
I found a website where I could pull historic close prices, and figured out how to alter the url based on four cell values (symbol, month, date, year) but got stuck there. Couldn't get the "get data from web" to recognize the URL in an automatic format, even with macros.
Background: it's a borrowing-base calculation for a stock-secured line's monthly covenant. If anyone has a better idea of how to set this up to pull the aforementioned close prices for multiple securities, please reply or PM me.
Thanks.
Try Yahoo Finance. It allows you to set the historical data range you need and then export it into excel.
Thanks, but I know where to find the data. It's a matter of importing the data automatically, since the number of securities is rather large. I'd like to avoid having to manually enter 50 stock prices each month.
Don't you have bloomberg api?
If my assumption is correct that bloomberg api pertains to a bloomberg terminal, then no. I don't work in IB, and thus don't have access to anything more sophisticated than the E.
Yeah, I made the URL dynamic based on cell inputs, in the sheet, but cannot figure how to make the data retrieve from the URL in the sheet. It's got a separate line that I can't get to reference cells. Only hard inputs, and thus fixed.
Yea, there is a way to do this.
I can't remember exactly what it is on a PC, but on a mac, you go to data, get external data, then select the range on the website. Then you run a query every time you want to update or set it to update at specific intervals.
My first thought was that you can use JAVA/C# or any other programming language to pull the information from the given website (bunch of places to find most of the code written...just google for it) and then have your programming language open up a given CSV file and insert your data there (again google for it and the code is there). The cool part of this is the flexibility it gives you.
In my 20 second search about your problem I found ... http://www.exceluser.com/explore/webqueries_1.htm which might seem like an easier place to start. If you have already tried this my bad.
sry DCO but web queries are a ridiculous way to it.. slow as hell.
it can be done with VBA btw. code is floating around somewhere on the web.
I think you can go to File--> Open and paste a URL and it will open that file off the internet.
Just use the Record Macro tool when doing this, and modify the code to do it for each of your stocks.
Record Macro
Open file
Paste your URL
Move that file's tab into your worksheet
Stop Recording Macro
then modify the code for you purposes
Perspiciatis tempore rem voluptas numquam. Ea molestiae qui numquam minima et.
Soluta repudiandae sint officiis voluptatem nobis minus culpa. Ipsa non necessitatibus amet neque. Qui quo sit soluta et molestiae non.
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...