Making VBA faster and non freeze

Making it not freeze would be outside the scope of a free forum, but the primary reason that it's so slow is that you're automating Internet Explorer. If you skip that step then it becomes much faster, you can use something like the below to retrieve the price from a ticker:

Please Login or Register  to view this content.

To reduce your code complexity, I'd have a VLookup in your summary table that brings in the actual ticker, this removes the lookup step and you only have to update the current row.
If you look at the above code, you'll see that it requires 2 calls, one to get the internal ID of the ticker and then another to the API service with the ID to get the price. You could halve the number of requests (and therefore significantly improve performance) by saving this internal ID in your tickers table, you'd then skip straight to this step:

Please Login or Register  to view this content.

Thinking about it, if you run through and get the IDs of the Tickers, you could do it all with a formula and Excel won't freeze, something like:

Formula:

Please Login or Register  to view this content.

I know, it was an example. Did you try any of it?

Why don’t you use the formula?

can you please incorporate formula in my excel sheet as I could not make it work

Have you done the first bit as outlined in my post where you've got the actual IDs for the tickers?

When you have, it's simply:

Please Login or Register  to view this content.

Where 44412 is the ID for CPSEETF

Originally Posted by Kyle123

Have you done the first bit as outlined in my post where you've got the actual IDs for the tickers?

When you have, it's simply:

Please Login or Register  to view this content.

Where 44412 is the ID for CPSEETF

Tried it did not work hence requested you to kindly use my excel sheet

Ah yes, I hadn't spotted you're using an old version of Excel.

In that case, you can simply use my code as a UDF, so add a column after A in your dashboard sheet, to bring in the Ticker then use the following in column B
=VLOOKUP(A5,Ticker!B:C,2,FALSE)

Then in column F, use the formula
=getNSEPriceForTicker(B5)

Of course if you really didn't want the additional column, you could combine the formulas to have the following in F:
=getNSEPriceForTicker(VLOOKUP(A5,Ticker!B:C,2,FALSE))

You really want to add the internal IDs to the listing table though, it will make it all much faster

Originally Posted by Kyle123

Ah yes, I hadn't spotted you're using an old version of Excel.

In that case, you can simply use my code as a UDF, so add a column after A in your dashboard sheet, to bring in the Ticker then use the following in column B
=VLOOKUP(A5,Ticker!B:C,2,FALSE)

Then in column F, use the formula
=getNSEPriceForTicker(B5)

Of course if you really didn't want the additional column, you could combine the formulas to have the following in F:
=getNSEPriceForTicker(VLOOKUP(A5,Ticker!B:C,2,FALSE))

You really want to add the internal IDs to the listing table though, it will make it all much faster

Great ! This does the trick, one last question. how do I set refresh every minute so that NSE refreshes