Data Refresh & Keep historical data

dallen8028

New Member
Joined
Jan 28, 2013
Messages
48
Good Morning Everyone, I am a missionary in Guatemala that needs to collect daily currency exchange rate from USD to GTQ. I have a spreadsheet it set up to reach out to a website for the daily exchange rate however, when I refresh for the daily rate, I have to manually recorded the data into today's cell. When I tried to automate this, the previous dates are either canceled or change to current dates data. I need to have the previous dates' data remain as history and change. How do I achieve this? I would love to upload my file but I don't see that I have that option. Please see what you can do with the image. Thank you in advance for your assistance.
 

Attachments

  • Data Refresh & Keep historical data Image.jpg
    Data Refresh & Keep historical data Image.jpg
    176.9 KB · Views: 31

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I am going to take a guess. Once you have the Date variable that is today's date and the today's exchange rate from the web site, you can worksheet date until the dates match and then plug the exchange value into the corresponding cell. As you appear to have VBA knowledge this response might be too simplistic. I suspect there is some piece of information missing that makes your problem more complex.
 
Upvote 0
Thank you RayFrye for your quick response. I am not sure what you are saying when you use the phrase "...you can worksheet date until the date match..." As far as having VBA knowledge, I do the research online to find what I am looking for, as someone somewhere has already invented a solution. I will then cut and paste and modify specific cell location(s). Otherwise I cannot generate my own VBA.
 
Upvote 0
I would love to upload my file but I don't see that I have that option. Please see what you can do with the image.
You can upload your file to a site like dropbox and paste the link here, or you could download the XL2BB add in and paste your data in a comment with that. Just click the XL2BB button that is with the format options for a reply/comment.
 
Upvote 0
Hi, Sorry an important word was missing... you can WALK the worksheet date until the dates match and then plug the exchange value into the corresponding cell.
 
Upvote 0
Hi, Sorry an important word was missing... you can WALK the worksheet date until the dates match and then plug the exchange value into the corresponding cell.
I think I understand what you are doing for the date, but how is that going resolve the auto fill option of the exchange rate for today's rate?
 
Upvote 0
I assume the exchange rate comes back in some form that can be read and loaded into a VBA variable. True?
 
Upvote 0
Please forgive my ignorance and thank you for your patience. The exchange rate and date comes back from the selected website just as you see in Cells B2-B4 via selecting the Data tab, Get Data, From Other Source, From Web. This portion has no VBA from me, all Excel.
 
Upvote 0
Ah, I am starting to get the picture; I can be a little slow sometimes. When you initially said you had code to reach out the the web site, I assumed that was automated within a VBA call to an API. Often software engineers over think a problem, we are god at taking a mole hill and turning it into a mountain. Is the " Data tab, Get Data, From Other Source, From Web" straight forward and a simple few clicks for you? If so, all you need (my thinking) is code to: Match the B3 date to a Dn date and drop B4 value into the En cell corresponding to the correct date. That's it?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top