Hi Terry, I am afraid we would not encourage VBA calls right now, but it is technically possible with some research. If you can post your usecase here I can try and give a solution without VBA.
I used to use the following VBA script to download 5-10 years of historical stock prices from Yahoo finance and have one tab (sheet) for each stock. However, this no longer works because Yahoo changed their API lately. I would like to automate my tasks by calling your functions if possible. Otherwise, I need to manually type the functions in cells, click menu items (or buttons) to get the data, copy the data to each tab, then do my own charting and analysis, which is tedious. (I haven't tried your product yet). Thank you. ================================================================ ticker="AAPL" end_date=today() start_date=today()-3650 DownloadURL = "URL;http://ichart.finance.yahoo.com/table.csv?s=" + ticker + "&a=" + StartMonth + "&b=" + StartDay + "&c=" + StartYear + "&d=" + EndMonth + "&e=" + EndDay + "&f=" + EndYear + "&g=" + data_frequency + "&ignore=.csv" With ActiveSheet.QueryTables.Add(Connection:=DownloadURL, Destination:=ActiveSheet.Range("A1")) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False 'True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "20" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With
Hi Terry, With MarketXLS, you can get historical data of upto 40 stocks with just 2-3 clicks. All you need to do is have the list of symbols in a list, select the cells with symbols and and click on Custom Dates, select the dates and click ok. All data is formatted nicely, date column is common for all symbols, you can select which data points in OHLCV you need, and most importantly you can select various granularity of data, like daily, monthly, weekly etc Checkout these pages... https://marketxls.com/forum/threads/getting-historical-stock-prices-in-excel.189/ https://marketxls.com/historical-stock-data-in-excel/ thanks
Again, the question: Is it possible to use all presented marketXLS functions within VBA modules. That includes getting fundamental historical data, historical prices etc. If one can do that for ona stock for one data, it is easy to get all data by more or less complex loops (cumpution time plays no role). The background of my question: I want to build a complex screener - and - I want to test the method by backtesting within the last 5 to 10 years based on a stock universe supported by yahoo (marjetXLS) in an automated way, that means without clicking with the mouse. I want to do it within VBA moduls (Programming my part): Defining (programming) screening conditions, choose universe (Symbols database e.g. SAP 500, Rusell 3000), number of stocks selected per period, nb of years to analyse - no more interaction while simulation is ongoing. Can this by done based on the market XLS function functionality and VBA programming (my part) ? Thanks
Hi Johannes, Yes, you can do that. In fact for that, a better way will be to use the API. So, you can call the functions using the URLs and create your own VBA. The marketXLS functions can also be called with VBA but it would not be that clean and fast as you would load the full library of marketxls (which includes a lot more than the functions) in the memory. We do have last prices, Historical Prices, Historical Fundamentals and everything available with easy to call urls as shown below... Look at this url below... Build, Collaborate & Integrate APIs | SwaggerHub I myself coded a bit in google sheets with this API. I can help and guide regarding that. Let me know what you think? Thanks Ankur
Hi Terry, thank you for this information. In the meantime I had also come across the offer regarding API. One more question: Are all fundamental data provided in the high level functions also accessible or derivable via API? Is a "call", for example, the query of the historical prices for a share for the last ten years (corresponds to a download on the website)? Estimation of "calls": Universe 5000 values: Historical Prices: 5000 Fundamental data for 10 years (one value, e. g. Price to Book): 5000x10 50000 "calls" does not seem very much against this background. Even if this effort only occurs once, it is too expensive for me with 55000*0,002 $ = 110 $. The minimum subscription for Pro is 12 months? Is it possible to buy a contongent, e.g. 200000 calls? Greetings and thanks in advance
Hi Johannes, Thanks for your questions. I have updated the pricing for the API.... MarketXLS API Honestly, we are just starting out with the API, so we are still like a bit fluid on our arrangements. But we are flexible and willing to work with you on this so it becomes worthwhile for both you and us. Here is my view on estimation on calls.... Universe 5000 values: Historical Prices - 5000 days in Past - One Symbol: 1 Call Fundamental data for 10 years (one value, e. g. Price to Book) for 1 Symbol : 1 Call The new pricing has a monthly plan. May be you want to start with a monthly plan and you try it out for a month? Not a huge cost anyway for a monthly plan. Kind regards Ankur
