Download Finviz Data into Excel

This Excel spreadsheet downloads stock quotes for 6855 companies from Finviz.com using VBA. The stock quotes includes over 65 pieces of financial data, including PE ratios, market capitalization, EPS, moving averages, earnings dates, average true range, and much more.

The VBA can be viewed or edited, and provides a basis for more sophisticated spreadsheets, including stock screeners and stock comparison spreadsheets. Similar techniques have been used to download Yahoo stock quotes and forex rates.

This is the VBA used in the spreadsheet.

Sub GetFinvizData()
 
Dim str As String
 
'Delete existing data
Sheets("Data").Activate 'Name of sheet the data will be downloaded into. Change as required.
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
 
'Download stock quotes. Be patient - takes a few seconds.
str = "http://finviz.com/export.ashx?v=151&c=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68"
QueryQuote:
            With Sheets("Data").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Data").Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
 
Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)
 
Sheets("Data").Columns("A:B").ColumnWidth = 12
Range("A1").Select
 
End Sub

The stock quotes includes these items: ticker, company name, sector, industry, country, market cap, P/E, Forward P/E, PEG, P/S, P/B, P/Cash, P, Free Cash Flow, dividend yield, payout ratio, EPS (ttm), EPS growth this year, EPS growth next year, EPS growth past five years, sales growth past five years, EPS growth quarter over quarter, sales growth quarter over quarter, shares outstanding, shares float, insider ownership, insider transactions, institutional ownership, institutional transactions, float short, short ratio, return on assets, return on equity, return on investment, current ratio, quick ratio, LT debt/equity, total debt/equity, gross margin, operating margin, profit margin, performance (week), performance (month), performance (quarter), performance (half year), performance (year), beta, average true range, volatility (week), volatility (month), 20-day simple moving average, 50-day simple moving average, 200-day simple moving average, 50-day high, 50-day low, 52-week high, 52-week low, relative strength index (14), change from open, gap, analyst recommendation, average volume, relative volume, price, change, volume, earnings data.

Download Finviz Data into Excel

Run the macro GetFinviz data to retrieve the stock quotes. The spreadsheet downloads 3.5MB of data, so the macro may take a few seconds (30 seconds on my Internet connection).

All the data in the spreadsheet can be saved and manipulated to develop your own stock screening spreadsheets. The stock quotes only need to be updated once a day (or whenever you require updated data).

Download Excel Spreadsheet to Retrieve Stock Quotes from Finviz


63 thoughts on “Download Finviz Data into Excel

  1. Hi guys, first I want to say that you made excellent work with spreadsheets.

    However, I found out that in the spreadsheets are missing dividend data – there is only dividend yield. Is it because FinViz doesn’t provide feed for data like “dividend per share” “annualized dividend” “ex-dividend day” etc? :/ Or is possible to add them there?

    Thanks for reply
    Arnie

  2. The multiple stock quotes spreadsheet doesn’t work for my mac, but this give me a master.
    Thank you, this is brilliant.

  3. Excel 2007 – gives compile error for this line of code

    With Sheets(“Data”).QueryTables.Add(Connection:=”URL;” & str, Destination:=Sheets(“Data”).Range(“a1”))

  4. Samir, I’m using this along with another spreadsheet to import data into MS Access so I can manipulate the fields to show all (or most) data for one stock on one page. I see in the FinViz website that there are some other indicators that I would like to add to your worksheet macro but I can’t find the relevant tags for them. I’ve spent about an hour ‘Googling’ for them but with no luck. Where did you find these?

    You have some great work on your site.

  5. Samir, this is excellent! Thanks so much for your work putting this together. One question. Is it possible to get this download but only for a specified group of stock symbols? If I wanted to do that, is there an easy tweak to the VBA to make that happen?

    Appreciate it!

  6. I got the same error as Matt when i copied/pasted from the web page. However, when I copied/pasted the macro from the spreadsheet after downloading it worked fine.

    Also, play around with the screener directly on the finviz website and you will get an idea of how to modify the macro to get different fields or specific tickers.

    e.g.using “v=152&t=MSFT&c=1,65,66” will get you ticker, price, change for just MSFT

    v=152&t=MSFT,PCLN&c=1,65,66 example for 2 tickers

  7. Excellent work. I have faster all the data I need.
    HELP: I am using a new sheet for calculations from the data sheet and it works fine, but when I renew the data sheet I am getting back error “price” at the cells of my calculations. Any idea?
    Thanks in advance

  8. Thank you Samir for the great work,

    I would like to call the data automatically. Every day / once weekly, each must be in an separate Table.

    how to do that with VBA

    regards

  9. Download Finviz Data into Excel

    Hello Samir, great work on website! However, the above excel doesnt work! Pls trouble shoot it as I need to download the data.
    Btw is the beta info included? What is the time period for the beta calculated?

  10. thanks Samir, just discovered the website and it is a fantastic resource! I built a similar sheet for extract from finance.yahoo I’m now certainly switching to yours.

    My question is: it is possible to use this method to extract stock info in excel versions for Mac?

  11. Seems like many of the spreadsheets do not work on Excel for Mac. Errors reference a module which accesses the Win32 API, which is obviously not available in OSX.

  12. Hi Samir,

    You have an excellent resource.

    Is it possible for you to develop an Excel sheet that downloads EDGARs financial statements into Excel (all 3 statements for say, past 5 years) ? And then, if I type in a formula it can calculate the values from the last 5 years ? E.g./ Operating Cash Flow – Capital expend / Current Liabilities, and it will plot this ratio over 5 years.

    Can you develop this ? Or is it available somewhere ? I’m happy to donate money for this.

    Thanks.

    Jerry
    Flaretoland at Hotmail dot com

  13. Hi Samir,

    I get a compile error in the following line of code (excel 2010). Do you have a clue how to rejig this ?

    With Sheets(“Data”).QueryTables.Add(Connection:=”URL;” & str, Destination:=Sheets(“Data”).Range(“a1?))

    Appreciate your help !
    Frederik

  14. Hello Samir,

    I have been using code based on your post for some time now. It used it to pull two columns of data for a stock: Col 1 is the ticker, and col 2 is the earnings growth estimate.

    Suddenly the code is no longer working and instead of returning the growth estimate, Finviz is returning some other basic info for the stock. For example

    No. No. Company Sector Industry Country Market Cap P/E Price Change Volume
    —————————————————————————————————————-
    1 AAPL Apple Inc. Consumer Goods Electronic Equipment USA 474675.89 13.25 524.99 -0.60% 11330200
    —————————————————————————————————————-

    Have you seen this problem? I hope that Finviz has not decided to disable customized web queries of their data 🙁

    Oh, and the string I am sending is …
    strURL = “http://finviz.com/export.ashx?v=151&t=” & TickerListForFinviz & “c=1,20”

    Regards,
    Doug

  15. Hello,

    Just came across this website today and find it quite interesting. Regarding the Download Finviz Data into Excel macro; in reading the comments I noted that people on Macs encountered errors when the macro is run. Out of curiosity I downloaded the macro and ran it in my version of Excel (Microsoft Excel For Mac 2011, ver 14.3.9) without any problems. I have a Mac Pro with OS X version 10.9 installed.

  16. Hello –

    Is there a place where debt amount can be downloaded for a publicly traded company? Yahoo or finviz does not seem to provide it.

    Does SEC have an API of sorts for data download?

    Thanks!

  17. Wow this is a wonderful routine and definitely works on Excel 2011 Mac. I just added a vlookup in another page and quickly sorted out the stocks I was interested in.

    Great job!

    -Tim

  18. Hi Samir,

    great job you are doing with excel.

    can “Download finviz data” be used for Futures and Forex on finviz?

    regards
    Ismail

  19. Hi Samir,
    Thank you so much for this. I have spent the last two weeks trying to figure out how to do this.

    For those who are copy and pasting the code above. The reason for the error in this line:
    With Sheets(“Data”).QueryTables.Add(Connection:=”URL;” & str, Destination:=Sheets(“Data”).Range(“a1”))

    Is & should only be &. That section should read {“URL:” & str, Destination}

    Also for folks who want to change which columns are displayed use the url that is generated when you change the “Setting” on Finviz. that will allow you to customize which columns are selected to download.

    Again, many thanks!

  20. The comment above didn’t come out right when posted the error when i pasted it was the & came up as the & along with the “amp”

  21. I noticed that some of the 52-week lows have more than 100% listed under them. That would mean there is a negative stock price when the stock hit its low. What exactly does that mean?

    1. I think Finviz just changed their web address for downloading. If you find out anything new let me know

      Thanks

      P.S. your spreadsheet was most useful. I enjoyed it while it lasted

  22. First of all, thank you so much for sharing this to the world. I appreciate your hard work and generosity. Thank you.

    When I run the macro today, I receive this error: “RUN TIME ERROR 1004, NO DATA SELECTED TO PARSE”. This worked prior to this day. Is FINVIZ disallowing the download of their data or is their a problem with the code?

  23. Not working any more for me… Looks like starting today nov 13th 2014 we have to enroll in the Finviz Elite (299$/year) in order to be able to be able download data from the website… can someone confirm this ? thanks

  24. This macro just stopped working and throwing the error: 1004 No data was selected to parse.

    The line of vba flagged was:

    Sheets(“Data”).Range(“a1”).CurrentRegion.TextToColumns Destination:=Sheets(“Data”).Range(“a1″), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=”,”, FieldInfo:=Array(1, 2)

  25. Thanks for the fantastic work.

    Seems the macro doesn’t work any longer…any thoughts? I’ve run the macro and I get a run time error ‘1004’: No data was selected to parse.

    Have been running the macro for a # of months with no issues, so I’m assuming this is something new on the finviz side of things? Thanks for your comments.

  26. I’ve been using this workbook for 1 ½ years and it has been an great way to retrieve data for a lot of stocks, but it appears Finviz has changed something in the URL and it no longer works. I’ve tried to figure out exactly what they changed with the hope of salvaging this workbook, but all my attempts to access anything related to ‘export’ leads to the webpage trying to sell subscriptions.

    I haven’t given up completely, but I suspect Finviz has stopped the free ‘export’ URL.

      1. Samir, you can see from the number of responses that there are quite a few people who used your workbook for bulk downloads. This is kind of like the loss of a child; almost. The bulk export has been shut down and I don’t see where this is possible even for paying subscribers. I’ve sent an email to Finviz inquiring about this but haven’t heard back yet. It appears Finviz only allows the ‘export’ feature for paying subscribers and from what I can read, only for screens, and in limited numbers (I think 100 rows).

        I have another workbook with a live data feed from ActiveTick and used your workbook as a database to run equations against, looking for stocks meeting certain criteria. The loss of this workbook was huge; all my previous trading activity was based on equations run against the data in this workbook.

        I have found a temporary solution by using a Refreshable Web Query. My initial attempt to use Data / From Web did not work, but copying and pasting the data in a worksheet and then setting up a Refreshable Web Query does work. You have to select the row above the table (example: “Total 6951 #2001”) to get the target table, but it works. The downside is that I will have to insert 348 Refreshable Web Queries to get all the available data. But it does include all 68 columns of data. It has taken me roughly 1 hour to insert the first 100 queries so my guess is that it will take me about 3 ½ hours total to complete this task, but considering how important this workbook is to my trading approach, I have little choice.

        I have tried altering your code to pull in successive queries but so far have been unable to figure out how to limit the call to table 11 which has the data. I’m not very good with VBA but I’m sure this can be done.

  27. Unfortunately, doesn’t work for me. Seems like Finviz moved Export function to the payable functionality. Could it be so?

    The html on Finviz says the following for @Export@ button:

    export

  28. Thanks Samir for the spreadsheets! It worked perfectly for me when Finviz was free. I am so disappointed like everyone else now…but just want to let everyone know that I just joined as a paid subscriber @ $39.50 USD a month. The spreadsheet works as normal now. My advice, if this is really worth it to you (it is to me), then it’s worth subscribing. My trading system depends on these large exports. Samir, if there is a way to still get this free again, please let us know! Thanks!

    1. Thank you for letting me know the spreadsheet still works if you subscribe.

      I can’t or justify or afford the $40/month for a subscription, but if you can, at least you now know the spreadsheet works

    2. Hi Theresa; thanks for the ray of hope…albeit at a cost. Like you, this spreadsheet has become such a huge part of my personal stock system that I feel compelled to belly up to the bar (so to speak). Although the monthly cost is $40 per month, it appears that the cost drops to $25 per month

      Since I’m not real proficient with VBA, would you mind sharing the alterations you made to the script in order to get it working. I’m assuming it relates to the URL and personalized login information but don’t understand this well enough to make that happen on my own.

      Samir, I just want to add my personal thanks for not only your original work, but for kindly sharing this with the world. It was a great “free” run while it lasted; I’m sure we would all appreciate any thoughts you can share related to modifications for those of us who decide to pay for the FinViz service.

      Thanks again for both of your help! David B.

      1. Sorry, I missed the end of a sentence. The cost drops to $24.92 per month if you are willing to pay for an annual subscription.

        David B.

    3. Hi Theresa. Is this still working for you? Did you have to change something in the code to make it work after subscribing to Elite? It does not seem to be acknowledging that I am logged in to my Elite subscription and therefore I am getting an error when I run the macro saying there is no data to parse.

      Thx for your help!

  29. yes, I confirm, you can still export the file with all the field from Finviz website directly if you subscribe to elite.
    But the VBA code doesn’t work anymore for me as the code need to be updated in order to use finviz username and password prior to be allowed to dowload.
    Does anyone have a the new VBA code that is using finviz elite login info?
    That will be very helpful
    thanks

  30. Ironic. I just discovered this a few weeks ago and was delighted. I began building a momentum trading system based on this amazing screening tool. Then it’s gone. Alas.

    Glad I didn’t have the system built and in actual use. I can see how someone who did would find it essential to pop for the $40/mo.

  31. Anyone here has the amended code? i have the elite account but when i do the extraction via web query it seems doesn’t remember my login hence the extraction keep failing.. Any help would be appreciated. Thanks

  32. Great looking spreadsheet – just what I’ve been looking for and thanks for sharing the VBA code.
    That said, when I try to execute the macro, I get a message that the data update failed and that I need
    to upgrade my browser. I’ve ran this on IE and Firefox at work, then Firefox at home (thinking some
    work constraints prevented it from working) with no success. Does this macro work only with a specific
    version of IE or Firefox, or do I need to adjust some option settings?

    I’m new to using Excel macros and VBA so bear with me.

    Thanks.

  33. Having the same issue. This spreadsheet is great. Did anyone come up with a solution? I’ve tried numerous browers as well. Thanks!

  34. Finviz have disabled the excel functionality and blocked a lot of the scrapers (see finviz.com/robots.txt).

    Anyone with knowledge of Python can build a scraper using BeautifulSoup, it’s not the difficult :^)

  35. Hello Samir,

    When attempting to update data, the functionality does not work as finviz.com blocks the requested query. Is there any work around to this issue. The following code applies:

    str = “http://finviz.com/export.ashx?v=151&c=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68”
    QueryQuote:
    With Sheets(“Data”).QueryTables.Add(Connection:=”URL;” & str, Destination:=Sheets(“Data”).Range(“a1”))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
    End With

    Any suggestion.

Leave a Reply to John Cancel reply

Your email address will not be published. Required fields are marked *

What is 7 + 4 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)