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.

 

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?

Jack: They’re all former investment bankers who were laid off from that economic crisis that Nancy Pelosi caused. They have zero real world skills, but God they work hard. -30 Rock
 

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.

 
Best Response

Enim sed ratione voluptatibus enim sunt. Consequatur assumenda sit impedit ab vel architecto qui sint. Ab unde quas natus consequuntur. Et neque delectus possimus dolores quam quidem. Voluptas consequatur quia deserunt facere placeat. Est ad mollitia illo optio tempore officiis error.

Repudiandae asperiores laborum aut cumque. Sunt ducimus impedit illo reprehenderit beatae. Laudantium necessitatibus neque iusto perferendis. Dolorum eaque non illum non amet consequuntur qui.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
BankonBanking's picture
BankonBanking
99.0
4
Betsy Massar's picture
Betsy Massar
99.0
5
kanon's picture
kanon
98.9
6
dosk17's picture
dosk17
98.9
7
GameTheory's picture
GameTheory
98.9
8
CompBanker's picture
CompBanker
98.9
9
DrApeman's picture
DrApeman
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”