Log In
Register
Home
August 11, 2019
Offline
Good morning:
I currently have a spreadsheet that utilizes Yahoo Finance to pull back the date and price of approximately 900 stock tickers. I do not use Microsoft's stock add-in because these tickers are mostly preferred shares, which are not supported by the add-in. Anyways, I've created a sheet that summarizes the ticker symbol, the most recent date of the data, and the most recent price.
When I try to refresh all data connections, it takes approximately an hour and a half to update. The following steps are what I have already done to improve performance. I have the 64 bit version of Excel on a 16GB RAM, i7 machine:
1. I eliminated the background refresh option, as suggested on this forum. That was a huge help.
2. I cut down on the data required to run the model. I started with 28 data points per query, and I am now down to 2. The size of the spreadsheet has gone from 3.5MB to 1.9MB.
3. I recorded a VBA script based on refresh all connections to test certain ideas (See Table.Buffer idea below).
I have noticed that in singularity, I am able to refresh a single web connection in about 3 seconds. I'm hoping to effectively scale that up to 45 minutes for the entire worksheet. I think the solution lies in amending the VBA code to suit. My current code reads.
ActiveWorkbook.Connections("Query1").Refresh
ActiveWorkbook.Connections("Query2").Refresh
ActiveWorkbook.Connections("Query3").Refresh
All the way down to Query 900.
I saw the Table.Buffer suggestion being mentioned on this site, but I am not sure how to implement it in the VBA code. I'm also not sure if the Background Refresh Option remains off when the VBA code runs. I'm sure there are other applications I may be able to turn off to speed up time. I have also thought about breaking the chunks of code into groups and insert a short wait time in the hopes that the macro would not move on until that group is refreshed.
Any suggestions are appreciated as I will be replicating this macro and testing everything suggested here. I appreciated your time in reading this and hopefully, your help in solving it. Thanks, Jeremy
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Jeremy,
Can you upload a sample file with 3-4 queries only?
There is no Table.Buffer in vba, it's a power query function, it should be used in your queries, all you can do in vba is to trigger the refresh.
But I guess that it might be possible to do things differently, in a single query, but I have to see some sample queries first.
August 11, 2019
Offline
I have added a sample file along with a sample txt file of the macro. I am beginning to think the current structure may be most efficient. Keep in mind that I have a 2 year old laptop that has 16GB of RAM and an i7 processor on a fiber Internet line, so my machine and Internet are about as good as I can get. Also, I have tried the If Error, Go to Next and Application.DisplayAlerts = False commands to eliminate my DataSource.Error notifications. They have not worked and any guidance there would be immensely helpful. Thanks so much, Jeremy
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Try the attached version, there is one query for all symbols.
August 11, 2019
Offline
This is excellent. It appears as if this method will speed things up two or three fold. I do need some direction on how to expand it and incorporate the other 880+ tickers. Any guidance you have would be great! Thanks, Jeremy
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
All you have to do is to paste symbols in the blue left table, and refresh the right green table.
Make sure the symbols are correct, they need o be exactly as they ill be used in building the link, otherwise the query will fail.
August 11, 2019
Offline
Thank you so much. I have been able to grow the database beyond 200, but now I am getting the attached image as an error message. It seems to be coming across inconsistently (sometimes it loads 19 data points, other times 230). The error stops the program from running altogether. I have a reliable Internet connection, so I'm wondering if there is a way to get the upload to ignore the data it cannot retrieve and upload everything it can update. Thanks again for all your help on this, Jeremy
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Make sure the symbols are the ones that can be used in link. For example, you had symbols like: ABR.A, but the link needs to be ABR-PA for this symbol, otherwise the link will not work. The query will take the symbols from the blue table and will use them to create the link:
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/" & Symbol & "/history?p=" & Symbol)),
August 11, 2019
Offline
Hi Catalin:
I think I did not do a good job of explaining the problem. I can load a table of 150 symbols just fine, then when I refresh again with the exact same symbols, I get the error with the exact same queries. I am getting errors more times than not as the size of the table grows. I think it has to do with the connection. The entire table refuses to load when I get these errors. I am wondering if there is way to work around that.
Jeremy
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Can you provide that table of 150 symbols you're using?
August 11, 2019
Offline
Sure. I have attached the latest table. Please ignore the values that are coming back blank. That is fine. The issue is the table will load with one refresh, then I get that error for the next three refresh attempts with the exact same data. Thanks so much for all your help on this.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Try V7 yahoo, see the file attached, should be faster, here is a sample link: https://query1.finance.yahoo.c.....ols=TLS.AX
Note that the symbols should not be altered as in the previous version, so ABR.A should stay the same, not ABR-PA
I added a step to remove the errors, only those without errors are returned. You can expand any field you want, I displayed them all.
August 11, 2019
Offline
Excellent. I have a couple of questions. Somehow, I managed to get my mind around some of the technicals of this version versus the previous one. When I run the queries, only the common stocks are returning. There are only 80 tickers updating out of the 900. When I dug into the GetData2 connection, I found that any column where there was no data (example: EarningsTimestamp) through the ticker out of the results. I tried to delete columns by editing the table in Query Settings and re-run the data, but I think I have to go inside the Data2 connection to do that. If you can explain to me how, I should be able to fine tune everything to meet my needs. Essentially, I need to remove the columns that are creating the errors which are allowing the data not to load.
Thanks again,
Jeremy
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
I removed the automatic Change Type step from GetData2 function, that had a reference to each column.
Answers Post
August 11, 2019
Offline
Thanks you so much for your persistence in getting this issue resolved. After a few hours using the file, I think you have solved my problem(s). I am going to be expanding the model with financial data in the coming weeks before I put it to full use. I am so happy I found you guys and grateful for all the work that was put into this. Thanks a million, Jeremy
1 Guest(s)
