Macro_Timer & subs
Budget: $30 – $250 USD
I have 2 Excel VBA macro subs (named GetTheData and SaveTheData) to download data and save results after the sheet recalculates. They are both listed below and work OK.
I use Excel 365.
I wish to accomplish the following:
Name the macro "Macro_Timer"
1) start the macro running at 06:35 each morning or when I open the file if after 06:35. I can easily add a button to start the macro.
a) If not too difficult, I'd like the time to start be the time in New York City, New York time 09:30 so that
no matter where I am globally, it starts with the stock market opening. Time in NYCity is GMT-4 except when switching to/from Daylight Saving Tme.
London and New York have different dates for switching to daylight savings time.
b) If 1a) is time consuming and expensive to do, I'd rather not spend the money now. Give me a cost for this option. Else I will just add a button to start the macro running when I open the file.
2) repeat the 2 subs every 5 minutes, where 00:05:00 is a variable I can easily change in the macro.
a) If cell AG6 > 40%, change the interval to 2 minutes
If cell AG6 < 10%, change the interval to 2 minutes
Revert back to 5 minute intervals when AG6 => 10% AND =< 40%
b) If cell AG14 > 40%, change the interval to 2 minutes
If cell AG14 < 10%, change the interval to 2 minutes
Revert back to 5 minute intervals when AG14 => 10% AND =< 40%
3) Open a Powerpoint file each morning, Filename = "Wall SP500 Images 2022 07 14" using today's date in that format
a) Copy the image of the chart (on Worksheet:"SPX Stocks", chart name:"SP 500" and is located in cells AP3:BF32) of the worksheet sequentially to a new slide in the Powerpoint file, use PNG or JPEG format, unless you have a good reason to use a different format.
Align "SP 500" chart center and middle on the PPT slide.
b) When saving a chart to Powerpoint, if the Interval for the current "SP 500" chart is the lower value (2 minutes), change the Powerpoint slide background to a pale color like
RGB Red 254 Green 241 Blue 206
My purpose is that when viewing the video, I'll instantly know to pay closer attention.
b) My objective is to create a short video each day. I don't know what Powerpoint transitions would work best yet, so let's start with using Fade, Duration=0.25, Advance slide after 00:00:02
3) stop the macro at 13:10, (or 16:10 New York time)
Sub GetTheData()
'
' GetTheData Macro
' Downloads the data into "NDW Data" worksheet
'
'
Sheets("NDW Data").Select
Application.Goto Reference:="R1C2"
Selection.End(xlDown).Select
Range("B11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RefreshLinkedDataType
Sheets("SPX Stocks").Select
End Sub
'
Sub SaveTheData()
'
' SaveTheData Macro
' Save graph data for each cycle
'
'
Application.Goto Reference:="R3C159"
Range(Selection, Selection.End(xlToRight)).Select
Range("FC3:ADS4").Select
Selection.Copy
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.Goto Reference:="R1C32"
End Sub
Give me 2 bids. 1) for doing all the work except 1a), and 2) including 1a)
I use Excel 365.
I wish to accomplish the following:
Name the macro "Macro_Timer"
1) start the macro running at 06:35 each morning or when I open the file if after 06:35. I can easily add a button to start the macro.
a) If not too difficult, I'd like the time to start be the time in New York City, New York time 09:30 so that
no matter where I am globally, it starts with the stock market opening. Time in NYCity is GMT-4 except when switching to/from Daylight Saving Tme.
London and New York have different dates for switching to daylight savings time.
b) If 1a) is time consuming and expensive to do, I'd rather not spend the money now. Give me a cost for this option. Else I will just add a button to start the macro running when I open the file.
2) repeat the 2 subs every 5 minutes, where 00:05:00 is a variable I can easily change in the macro.
a) If cell AG6 > 40%, change the interval to 2 minutes
If cell AG6 < 10%, change the interval to 2 minutes
Revert back to 5 minute intervals when AG6 => 10% AND =< 40%
b) If cell AG14 > 40%, change the interval to 2 minutes
If cell AG14 < 10%, change the interval to 2 minutes
Revert back to 5 minute intervals when AG14 => 10% AND =< 40%
3) Open a Powerpoint file each morning, Filename = "Wall SP500 Images 2022 07 14" using today's date in that format
a) Copy the image of the chart (on Worksheet:"SPX Stocks", chart name:"SP 500" and is located in cells AP3:BF32) of the worksheet sequentially to a new slide in the Powerpoint file, use PNG or JPEG format, unless you have a good reason to use a different format.
Align "SP 500" chart center and middle on the PPT slide.
b) When saving a chart to Powerpoint, if the Interval for the current "SP 500" chart is the lower value (2 minutes), change the Powerpoint slide background to a pale color like
RGB Red 254 Green 241 Blue 206
My purpose is that when viewing the video, I'll instantly know to pay closer attention.
b) My objective is to create a short video each day. I don't know what Powerpoint transitions would work best yet, so let's start with using Fade, Duration=0.25, Advance slide after 00:00:02
3) stop the macro at 13:10, (or 16:10 New York time)
Sub GetTheData()
'
' GetTheData Macro
' Downloads the data into "NDW Data" worksheet
'
'
Sheets("NDW Data").Select
Application.Goto Reference:="R1C2"
Selection.End(xlDown).Select
Range("B11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RefreshLinkedDataType
Sheets("SPX Stocks").Select
End Sub
'
Sub SaveTheData()
'
' SaveTheData Macro
' Save graph data for each cycle
'
'
Application.Goto Reference:="R3C159"
Range(Selection, Selection.End(xlToRight)).Select
Range("FC3:ADS4").Select
Selection.Copy
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.Goto Reference:="R1C32"
End Sub
Give me 2 bids. 1) for doing all the work except 1a), and 2) including 1a)