How to Download Yahoo Finance Historical Data Automatically Stock Prices Fixed Excel VBA

Posted by Nonaluuluu on Friday, December 6, 2019 · Leave a Comment

The below video shows you how to download yahoo finance stock price historical data automatically with Excel VBA. The how-to instructions are under the video. You can control your mouse with Excel and programmatically enter data into an internet browser with my code:

The below code will enable you to automatically download historical data from finance.yahoo.com for an unlimited number of stocks. Historical data includes the stock open and closing prices and volume for each day that passes in between the given date criteria filter. The other popular VBA Excel programs that previously worked for pulling Yahoo Finance data broke because the API no longer works, so I built a new workaround that lets you control the mouse with Excel and download stock data for stocks up until row 1,048,576 in Excel. If you ever would want more rows than that, you could leave a comment on my Youtube video and I'd set it up for you. This new solution works for 2019 and 2020.

Before copying my code I recommend you set-up the following https://getadblock.com/ Ad Blocker for Google Chrome (It's the #1 ad blocker on Google Search).

It ensures that Yahoo Finance loads like the below image:

I also recommend that you set the option on Taskbar, which is the bottom bar on your computer, to not auto-hide. This option ensures that "File Explorer" selects properly. Search for File Explorer in the Windows search-bar and pin it to the Taskbar.

Taskbar:

File Explorer:

Also, ensure that you include a Userform in your Visual Basic Editor for your program. Once you add the Userform, then you can add the Tools > Reference > Microsoft Forms 2.0 Object Library as a reference. This option isn't there unless you add a Userform, which you can conveniently learn how to do in my Userform video on my Youtube! The other references you will need include the below. If you're unsure of how to install references, then I recommend that you follow this page's video: https://vbatutorialcode.com/visual-basic-editor/ .

Userform:

Other References:

Lastly, you have to go to your task manager by pressing Alt+Control+Delete and selecting Task Manager. Expand the dropdown under Apps for Google Chrome, Right click Google Chrome, select Properties, and copy that file location that I highlighted Yellow in the third image below. Paste that link in the code section near the vPID since different versions of Windows have Chrome in different locations:

Now that you have that all set-up. The below code works on Google Chrome browser. I recommend that you close your current File Explorer and Google Chrome browsers before running. You can mess around with the cursor positions to line it up on your computer.

PLEASE NOTE THAT YOU CAN USE THE BELOW CODE IN PLACE OF THIS MANY SCREEN X,Y SELECTIONS:

Application.SendKeys ("^c") 'Sends Control+C to browser to copy
Application.SendKeys ("^v") 'Sends Control+V to browser to paste
Application.SendKeys ("~") 'Sends Enter to browser
Application.SendKeys ("{NUMLOCK}") 'NUMLOCK per insider trading video

IF you struggle copying the below code, I recommend removing the underscore & _ and putting those lines on the same line. Underscore in VBA denotes ("continued on next line") and ampersand denotes a concatenation. Ampersand, or &, concatenates the first line with the second line with the _, or underscore, since sometimes lines don't fit on the same line. Ampersands are also useful when working with variables and row numbers in particular columns if you need a variable A1:A4 as "A" & variable & ":" & "B" & variable2.

Code:


Private Declare Sub mouse_event Lib "user32" & _
(ByVal dwFlags As Long, ByVal dx As Long, & _
ByVal dy As Long, ByVal cButtons As Long, & _
ByVal swextrainfo As Long)
Private Const mouseeventf_leftdown = &H2
Private Const mouseeventf_leftup = &H4
Private Const mouseeventF_Rightdown As Long = &H8
Private Const mouseeventF_rightup As Long = &H10
Private Const MOUSEEVENTF_MOVE = &H1 '  mouse move
Public Declare Function SetCursorPos Lib "User32.dll" & _
(ByVal X As Integer, ByVal y As Integer) As Long


Declare Sub sleep Lib "kernel32" (ByVal dwmilliseconds As Long)

Public Declare Function GetCursorPos Lib "user32" & _
(lpPoint As PointAPI) As Long
Public Type PointAPI
X As Long
y As Long
End Type

Sub aaa()






Dim iteration As Double
iteration = 1
erowstocks = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Do Until iteration > erowstocks
If yuh = 1 Then
GoTo line2:
End If

Range("A" & iteration).Select
Selection.Copy

If iteration > 1 Then
GoTo line3:
End If


Dim vPID As Variant
'Variation 1:
vPID = Shell("C:\Program Files (x86)\Google\Chrome\Application\" & _
"Chrome.exe -URL http://finance.yahoo.com", vbNormalFocus)

'Shell ("C:\Program Files (x86)\Google\Chrome\Application\" & _
"Chrome.exe -url http:finance.yahoo.com")
line3:

Application.Wait (Now + TimeValue("00:00:06"))

 SetCursorPos 700, 146 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&

mouse_event mouseeventF_Rightdown, 0&, 0&, 0&, 0&
mouse_event mouseeventF_rightup, 0&, 0&, 0&, 0&
mouse_event MOUSEEVENTF_MOVE, 12, 80, 0, 0
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
SetCursorPos 1050, 146 'x and y position
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
 SetCursorPos 850, 612 'x and y position 612
 Application.Wait (Now + TimeValue("00:00:09"))
 mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
 SetCursorPos 1175, 850 'x and y position
  Application.Wait (Now + TimeValue("00:00:09"))
   mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
  Application.Wait (Now + TimeValue("00:00:09"))
 



If iteration = 1 Then
GoTo line1:
End If

Range("A1").Select



Workbooks(2).Activate

line2:

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A1").Select
Range("A1:E" & erow).Select
Selection.Copy

If iteration = 1 Then
Workbooks("FirstCodeBook.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Else
Workbooks("FirstCodeBook.xlsm").Activate
Range("A" & counter).Select
ActiveSheet.Paste
End If


counter = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
counter = counter + 1

iteration = iteration + 1
yuh = yuh + 1
Loop




Dim y As Double
y = 1
Do Until y > 10
y = y + 1
Loop
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&




    
line1:
 Call Shell("TaskKill /F /PID " & CStr(vPID), vbHide)
 Workbooks.Open Filename:="C:\Users\Nonaluuluu\Desktop\SecondCodeWorkbook.xlsm"
 Range("AF1").Value = erowstocks
 Range("AF2").Value = vPID
Application.Run "SecondCodeWorkbook.xlsm" & "!secondmodule"
 
 End Sub

So that's not the full end of the code. I also have a second workbook located in this path "C:\Users\Nonaluuluu\Desktop\SecondCodeWorkbook.xlsm" called SecondCodeWorkbook.xlsm. Obviously replace Nonaluuluu with the username of your computer when copying the path for where the book is located. If you're unsure of how to get paths for where workbooks are saved, then I recommend that you watch this video: https://www.youtube.com/watch?v=jnbZnw3SyLs

Here's the code for the SecondCodeWorkbook. Sidenote: The code Application.run "SecondCodeWorkbook.xlsm" & "!secondmodule", which is the 2nd to last line of code, runs the second module from the 2nd workbook from the 1st workbook. Per my Procedure Too Large Video on my Youtube, after like 3000 lines of code in VBA, Excel VBA stops working so you can have multiple workbooks. There is obviously only like 500 lines of code here, so I didn't need to have the second workbook. I did it anyway to teach you how to do this instead of getting caught off guard. This workbook should have the same references (and is the one that needs the Userform). It needs a Userform so that you can pull the right-clicked file name from Downloads folder name from the Clipboard and paste it into Excel. Everything that you copy on your PC is momentarily stored on a clipboard. By pulling the name from the clipboard, you don't interrupt the code running. The userform lets you enable the Microsoft Forms 2.0 Object Library Tool which lets you pull data from the Clipboard without interruption.

Second Workbook code:

Private Declare Sub mouse_event Lib "user32" _
(ByVal dwFlags As Long, ByVal dx As Long, _
ByVal dy As Long, ByVal cButtons As Long, _
ByVal swextrainfo As Long)
Private Const mouseeventf_leftdown = &H2
Private Const mouseeventf_leftup = &H4
Private Const mouseeventF_Rightdown As Long = &H8
Private Const mouseeventF_rightup As Long = &H10
Private Const MOUSEEVENTF_MOVE = &H1 '  mouse move
Public Declare Function SetCursorPos Lib "User32.dll" _
(ByVal X As Integer, ByVal y As Integer) As Long




Sub secondmodule()



 SetCursorPos 1530, 485 'x and y position
 
   Set myWindow = ActiveWindow
  
    With myWindow
        .Top = 100
        .Left = 100
    End With


Application.DisplayAlerts = False

Workbooks("SecondCodeWorkbook.xlsm").Activate
erowstocks = Range("AF1").Value


 SetCursorPos 460, 1050 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:02"))
SetCursorPos 40, 740 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:02"))
SetCursorPos 280, 145 'x and y position
mouse_event mouseeventF_Rightdown, 0&, 0&, 0&, 0&
mouse_event mouseeventF_rightup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 360, 640 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 450, 230 'x and y position 450
mouse_event mouseeventF_Rightdown, 0&, 0&, 0&, 0&
mouse_event mouseeventF_rightup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 480, 300 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 640, 160 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Workbooks("SecondCodeWorkbook.xlsm").Activate
AppActivate Application.Caption
SetCursorPos 700, 400 'x and y position
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
SetCursorPos 700, 400 'x and y position
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&

'make sure you add a userform to your visual basic editor and enable 'microsoft Forms 2.0 in the tools/references
Dim DataObj As MSForms.DataObject
 Set DataObj = New MSForms.DataObject
 DataObj.GetFromClipboard

strpaste = DataObj.GetText(1)

Range("I3").Value = strpaste


Dim workbookname As String
workbookname = Range("i3").Value

Workbooks.Open Filename:="C:\Users\Nonaluuluu\Downloads\" & _
workbookname & ".csv"
Workbooks(workbookname & ".csv").Activate
Dim yuhvariable As Double
yuhvariable = 80
Dim yuh As Double
yuh = 1
AppActivate Application.Caption
Do Until Iteration > erowstocks
If yuh = 1 Then
Iteration = 1
counter = 1
GoTo line2:
End If
Workbooks("FirstCodeBook.xlsm").Activate
AppActivate Application.Caption
Range("A" & Iteration).Select
Selection.Copy

If Iteration >= 2 Then
yuhvariable = 58
End If




Shell ("C:\Program Files (x86)\Google\Chrome\Application\Chrome.exe -url http:finance.yahoo.com")
line3:

Application.Wait (Now + TimeValue("00:00:06"))

 SetCursorPos 700, 150 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&

mouse_event mouseeventF_Rightdown, 0&, 0&, 0&, 0&
mouse_event mouseeventF_rightup, 0&, 0&, 0&, 0&
mouse_event MOUSEEVENTF_MOVE, 12, yuhvariable, 0, 0
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
SetCursorPos 1050, 146 'x and y position
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
 SetCursorPos 850, 612 'x and y position 612
 Application.Wait (Now + TimeValue("00:00:09"))
 mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
 SetCursorPos 1175, 850 'x and y position
  Application.Wait (Now + TimeValue("00:00:09"))
   mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
  Application.Wait (Now + TimeValue("00:00:09"))

 
 'If Iteration > 1 Then
 'SetCursorPos 480, 1050 'x and y position
'mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
'mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
'GoTo line9:
'End If
 
 
SetCursorPos 460, 1050 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
line9:

Application.Wait (Now + TimeValue("00:00:02"))
SetCursorPos 40, 740 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:02"))
SetCursorPos 280, 145 'x and y position
mouse_event mouseeventF_Rightdown, 0&, 0&, 0&, 0&
mouse_event mouseeventF_rightup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 360, 640 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 450, 230 'x and y position
mouse_event mouseeventF_Rightdown, 0&, 0&, 0&, 0&
mouse_event mouseeventF_rightup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 480, 300 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
'SetCursorPos 245, 210 'x and y position
'mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
'mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Application.Wait (Now + TimeValue("00:00:01"))
SetCursorPos 640, 160 'x and y position
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
Workbooks("SecondCodeWorkbook.xlsm").Activate
AppActivate Application.Caption
SetCursorPos 700, 400 'x and y position
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&
SetCursorPos 700, 400 'x and y position
Application.Wait (Now + TimeValue("00:00:01"))
mouse_event mouseeventf_leftdown, 0&, 0&, 0&, 0&
mouse_event mouseeventf_leftup, 0&, 0&, 0&, 0&

'make sure you add a userform to your visual basic editor and enable microsoft Forms 2.0 in the tools/references

 Set DataObj = New MSForms.DataObject
 DataObj.GetFromClipboard

strpaste = DataObj.GetText(1)

Range("I3").Value = strpaste



workbookname = Range("i3").Value
Application.Wait (Now + TimeValue("00:00:03"))
Workbooks.Open Filename:="C:\Users\Nonaluuluu\Downloads\" & workbookname & ".csv"
Workbooks(workbookname & ".csv").Activate



line2:

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A1").Select
Range("A1:G" & erow).Select
Selection.Copy

If Iteration = 1 Then
Workbooks("FirstCodeBook.xlsm").Activate
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
erow2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("H1:H" & erow2).Value = workbookname
Else
Workbooks("FirstCodeBook.xlsm").Activate
Sheets("Sheet2").Select
Range("A" & counter).Select
ActiveSheet.Paste
erow2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("H" & counter & ":" & "H" & erow2).Value = workbookname
End If
counter = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
counter = counter + 1

Sheets("Sheet1").Select
Workbooks(3).Activate
ActiveWorkbook.Close False



Iteration = Iteration + 1
yuh = yuh + 1



Loop



End Sub

Category: VBA · Tags: 2020, automatically, download, excel, finance, Fixed, historical data, How to, stock prices, VBA, yahoo

Comments are closed.

How to Download Yahoo Finance Historical Data Automatically Stock Prices Fixed Excel VBA

Search

Excel VBA Filter