Pulling real time data from excel on a daily basis (stock data)

[Irish15]

Hello,

I'm using Excel to pull in real time data for the stock market. It's working great and I have data in categories that gives me gains/losses broken down for each area I want. What I would like to do is pull that data daily into another worksheet so I can create a line chart. Basically, get the result of each day to create a visual chart. I don't need a chart of the "current data" but what I would like to do is pull the current data on a daily basis (at the end of the market) keeping the data concrete and then create a visual chart from that over time. I was told there is a way to do that with VBA Macros.

Would someone be able to help me with the VBA code? I have attached a sample spreadsheet. If I have the right code, I should be able to setup a windows scheduler from there.

Thank you

[Greg M]

Hi there,

See if the following code does what you need:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Irish15]

Works perfectly! Thank you!

Does this run at a specific time of day? I ran it manually and it worked fine and that is ok if that is the case and I can work on it.

[Irish15]

Nevermind, I see that is what the Windows task scheduler will do. This stuff is all new to me. Thanks again!

[Irish15]

I have just one question. If I decided that I wanted to include other totals in this how would I add those to this VBA code? Could I just insert the extra cell and column information in the section or should I create another module for that?

For example, can I just add another subtotal column and subtotal cell? It would be the same date.
Const sDAILY_DATA_SHEET As String = "Sample Spreadsheet"
Const sGRAPH_DATA_SHEET As String = "Gain-Loss Data"
Const sCOLUMN_SUBTOTAL As String = "B"
Const sSUBTOTAL_CELL As String = "I8"
Const sCOLUMN_DATE As String = "A"

[Greg M]

Hi again,

Thanks for your feedback.

I'd need a bit more information in order to answer your latest questions - e.g. Where are the extra subtotals located? Are they all on the same row? Will they always be in those locations? Where do you want them copied to? To the same "last row" on the Graph Data worksheet? To the next free row? etc. etc.

What you need can almost certainly be accommodated, but it'll be much easier if we keep the mind-reading to a minimum

Irish15? Do you have a connection?

Regards,

Greg M

[Irish15]

Thanks for the reply. I have a dashboard setup where I can pull all the info easily (all on the same spreadsheet so the daily data sheet row could stay the same). Basically, I have 3 other totals I would like to grab and pull over to the same "gain-loss data" spreadsheet. So, that would be 3 extra columns to the code you wrote. The date would be the same.

I tried playing around with it but just kept messing it up. The best I got was to add 4 separate modules with the same code you provided but each total goes to a new line and the same date is repeated. I could just keep them all on the same row but not sure if that is possible. I can certainly work with that but not sure if it is easy to just add these to one module somehow.

Hope I explained that correctly. Thanks again for your help with this!

[Irish15]

If helpful, here is pic of what I have.

[Greg M]

Hi again,

This is a different approach, so see if the following code does what you need (I've just put in "guess values" for the locations of the various subtotal cells):

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[Irish15]

Works perfectly! Thanks so much. Would have never figured it out on my own. I should have mentioned the extra cells the beginning so thank you for the extra work.

Also, just noticed you are from Dublin. Awesome! I have family from Limerick.

Cheers mate

[Greg M]

Hi again,

Many thanks for your feedback.

You're welcome - glad I was able to help.

Ok on Limerick - in terms of US distances Limerick is only "around the corner" from Dublin!

Regards,

Greg M