Automate record of data in excel

[amittal6]

Hi,

Could I ask you if there is a way to automate the data in a row to be saved automatically every month in a separate tab?

For example, if I am maintaining a finance portfolio, I want to save the portfolio value on the 1st of every month in a table of different tab and see how it is changing month-on-month.

Is there a way I can automate this data record in excel? I'd greatly appreciate it.

 

[vletm]

amittal6​

... or could You filter the 1st of every months data and see how it is changing month-on-month?

 

[amittal6]

amittal6​

... or could You filter the 1st of every months data and see how it is changing month-on-month?

Thanks for your suggestion. It’s the same sheet which has live data like stock prices. What I am looking for on 1st I just capture the snapshot. So $100 become $95 on 1st May, $102 on 1st June and so on. I’ll then analyze it after automated capture. Hope that clarifies

 

[vletm]

amittal6​

You should able to send a sample Excel-file, which shows Your live data
as well as
which data should save to other sheet?
What happens if there are none data the 1st day of month?

 

[amittal6]

Nice idea. Sending a sample file shortly.

 

[amittal6]

amittal6​

You should able to send a sample Excel-file, which shows Your live data
as well as
which data should save to other sheet?
What happens if there are none data the 1st day of month?

Hi

vletm​

See the attached file. First tab is where the live data is there which changes every working day. Second tab is source values - updated externally using refresh. Third tab has the table where I want to capture the information regularly and automatically.

 

[vletm]

amittal6​

You could test this sample file.
I cannot verify how do it work live.
# You've asked to save something monthly ... so Your expected next save would come few days later.
# This sample file tries to save daily ... then You could notice tomorrow - do it work as You've written?
# Of course, this file have to be open/active each day.

 

[amittal6]

Thank you. May be I am bit novice in automation. I checked your sheet. Not sure if I understand your file.

 

[vletm]

amittal6​

What do You understand from it?

It could add one value to the third tab.
# every months first day ... that means next Wednesday - okay?
# but for that moment, it takes three days - okay?
## that's why it should add every day one value to the third tab ... to verify, do it work.
Above adding depends, can that file get data ... do 2nd tab refresh ... do 1st tab refresh.
That's why, that file should be open daily and ready for use.
You haven't written any ideas, how do You have gotten that data before my sample version.
>>> New sample version.
>>> I added two options there - automatic enable connections & refresh.
>>> You should verify those connections Yourself - I cannot check those myself. <<<

 

[vletm]

amittal6​

Did You test it today?
I cannot test & verify it with my Excel!

 

[amittal6]

amittal6​

Did You test it today?
I cannot test & verify it with my Excel!

Hi vletm

Thank you for following up. Honestly, I tried to test it, but looks like I am not that well conversant with Excel, especially the AUTOMATE tab.
I am sorted with connections to fetch the data from required sources and refresh them daily, but when it comes to creating 'script' in the automate tab, I feel I need some handholding. That is where I am stuck, hence I did not respond to you. Appreciate your concern.

 

[vletm]

... hmm?
AUTOMATE tab ... my sample file do not have that named sheet!
Have You tried to use my sample file at all?
... if You have ... or want to change anything visible eg rename sheets from that ... it won't work!
Here the newest version.