Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Get price for a index from yahoo finance for a specific day

0

Hi

I am working on a project which requires to get a price of a index on a specific day like for every last working day of the month. I use Yahoo finance to get the data and its a long process to update lot of price of indexes going one by one. Is there any way i can update the prices in one go with help of  macro. Below is the format i use to update and i will have to update a line below for next month (Jan'22) and update the prices of indexes. 

sample file is attached   

Any help is appreciated

excel
posted by: maviehs on Sat Jan 1, 2022 at 5:28 pm
Answer
Discuss

Answers

0

Hi Hemant and welcome to the Forum.

I'm not a user of Yahoo Finance but please look at Don's tutorial here: Easy Excel Web Import - that shows you how to use a macro to download prices from that Yahoo Finance site.

People have had some problems with Yahoo Finance and -if you do- you'll find some previous Forum questions/answers e.g. Fetching Online Price (Yahoo Finance), VBA Error 91 "Object variable or with block variable not set" (in which I'm pretty sure Variatus meant to say Yahoo stopped bulk data scraping in 2018 not 1918!).

You say your file Sample1.xls has a problem in that the VLOOKUP formula can't return more than a few month's data - that can't be helped unless you add data to your sheet/ table. In the revised file attached, I've put an updating fromula in column F (and pointed the VLOOKUP to that column). The formula in cell  F3 is:

=EOMONTH(NOW(),2-ROW())
where EOMONTH delivers the date of the End Of Month based on NOW() today's date less (second argument) a number of months. For F3 2- ROW() gives 1 (so end of last month. In F4, the ROW is $ so it's 2 months back etc. That means row 3 will become 31 January when the date is 01 Feb  to 28 Feb.

In your VLOOKUP formula, I've wrapped around IFERROR so it becomes (e.g. in B3):

=IFERROR(VLOOKUP($F3,Table_0__2[#All],4,0),"Need data")
That means on 01 Feb, the row dates (and prices) will move down a month and row 3 will show the error message "Need data" or whatever you like.

Hope this helps.

posted by: John_Ru (rep: 6142) on Sun Jan 2, 2022 at 3:45 am
Discuss

Discussion

Hi John.. thanks for answering the question however i dont require data for all the days. Its just last working day for all the index and that too only the closing price. Even if i download the table as shown in the example ,the problem with above solution is that the table is fixed with 100 rows and data prior to that day  will be deleted. What i think a simple solution is to build a macro which goes to historical page of the index ,take the last working day of previous month and paste it the cell of excel sheet
maviehs Jan 2, '22 at 8:12 am
Hemant. I'm out for the day now but if you upload your Excel file which gets all the historical data you need,  I'll show you (tomorrow) how to extract the last day closing price and put it into a cell. 
John_Ru (rep: 6142) Jan 2, '22 at 8:49 am
I have attached an Excel sheet as a sample. in ell A2 it is mentioned as Dec21 and against this all five columns where i requires the last working day price from index as highlighted above
maviehs Jan 2, '22 at 8:53 am
I saw that (and can see it on my phone) but wanted a file where you'd used the tutorial macro to extract all the historical data (I don't plan to do that for you!) so I could locate and put the data into your cells. 
John_Ru (rep: 6142) Jan 2, '22 at 9:15 am
I am attaching the file. I tried to do a vlookup but it wont give any results after three month cuz the data will exceed the table
maviehs Jan 2, '22 at 11:03 am
Can't check now but expected a. xlsm file (so I could edit your macro). 
John_Ru (rep: 6142) Jan 2, '22 at 11:24 am
i saw the video and don updated the data from query so i link the file from site to get the historical data. If you have any other video then i will try to work on that too
maviehs Jan 2, '22 at 11:27 am
any update on the file ?
maviehs Jan 4, '22 at 9:22 am
See my revised Answer/ file- can't see how else I can help.
John_Ru (rep: 6142) Jan 4, '22 at 2:14 pm
their is a way .. if we can paste value the amounts for October (via MACRO) if we work for December then the values will freeze. MACRO will have to check the last line and then go two lines above and paste value the formula so it wont throw "need data" or any error. Then the MACRO can copy the formula for Jan from December and the table will update the value.
I hope i am making sense
maviehs Jan 4, '22 at 2:59 pm
Hemant. I'm not following you really. Please confirm which of Don's tutorials you are referring too (send the URL) and I'll see if I have any time to solve your problem.
John_Ru (rep: 6142) Jan 4, '22 at 4:06 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login