Building in Currency Impact (Operational Change) to Access Query
I am going to try to explain my current process for compiling travel spend for my organization globally and see if there is an easier way to consolidate this process from using both Access and Excel to only needing to use Acces as a one stop shop.
Right now we have monthly detail that is summary level and it is appended each month to an existing database. Thre are fileds for country (ISO number), company number, ledger date, expense code, spend, count, and a final field called "month2" that is blank to start but filled in using a update query in Access.
The bottomline is I currently use both Access and then Excel to go beyond just a net month to month and year or year change for each company on the extract.
I used Access to consolidate both the Raw currency totals and also the USD totals using a master table of all country and months and the currency rates.
The GL date is covered into a GL month based on the universal calendar using a update query like I mentioned.
I take the results of this file and end up with a 2 tabs on my excel sheet that has country, company number, spend total (raw tab 1 and USD tab 2).
The process to get to those two tabs is to create a sheet that has the month to date value and then a total of the months that make up YTD for if it was April I have to use a macro in excel to creat a sheet that hs the total of Jan Feb Mar and Apr for 2010 and 2011. Along side with the current month total Apr 2010 and Apri 2011 to do my MOM and YOY comparisons.
The excel sheet is set up with tabs for every country so that I can figure out an operational change dollar amount using the following formula.
(Current Period Spend Total - Previous Period Spend Total)*Previous Period Currency Rate
This by expense type as part of the raw file.
The problem is I do seperate calculation for the month to date based on the previous period rate and then the YTD is a blended average of the previous years months so it would be the currency rate blended average for January through April 2010 in this example that you multiple the YTD total by.
All of the Op change dollars are added and then used to do the following calculation to get an operational change percentage to compare the Net change to get the currency impact due to currency flucuation over the time period.
I know this is a lot of information here but I have made some attempts and housing this whole process in Access with very little success.
If any more details are neded or you want to see a mock up of a file let me know.
Right now we have monthly detail that is summary level and it is appended each month to an existing database. Thre are fileds for country (ISO number), company number, ledger date, expense code, spend, count, and a final field called "month2" that is blank to start but filled in using a update query in Access.
The bottomline is I currently use both Access and then Excel to go beyond just a net month to month and year or year change for each company on the extract.
I used Access to consolidate both the Raw currency totals and also the USD totals using a master table of all country and months and the currency rates.
The GL date is covered into a GL month based on the universal calendar using a update query like I mentioned.
I take the results of this file and end up with a 2 tabs on my excel sheet that has country, company number, spend total (raw tab 1 and USD tab 2).
The process to get to those two tabs is to create a sheet that has the month to date value and then a total of the months that make up YTD for if it was April I have to use a macro in excel to creat a sheet that hs the total of Jan Feb Mar and Apr for 2010 and 2011. Along side with the current month total Apr 2010 and Apri 2011 to do my MOM and YOY comparisons.
The excel sheet is set up with tabs for every country so that I can figure out an operational change dollar amount using the following formula.
(Current Period Spend Total - Previous Period Spend Total)*Previous Period Currency Rate
This by expense type as part of the raw file.
The problem is I do seperate calculation for the month to date based on the previous period rate and then the YTD is a blended average of the previous years months so it would be the currency rate blended average for January through April 2010 in this example that you multiple the YTD total by.
All of the Op change dollars are added and then used to do the following calculation to get an operational change percentage to compare the Net change to get the currency impact due to currency flucuation over the time period.
I know this is a lot of information here but I have made some attempts and housing this whole process in Access with very little success.
If any more details are neded or you want to see a mock up of a file let me know.
ASKER
I have two separate steps for getting to this final calcuatlion (Some in Access and then in Excel) and I am trying to determine if Access has any way to easily combine the two together. it would save a lot of time and manual intervention.
Thanks,
Adam E
Thanks,
Adam E
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try to be less ambiguous. Overall, I just wanted to know if this was possible to contain all of this an Access database.
Currently, the Excel component is used to take the totals by Raw Currency and USD for each country and calculate the amount of operational change dolloars associated with the spend from the old period to the new period (i.e March 2010 vs March 2011 and YTD 2010 and YTD 2011). This Operational change dollar is compared to the actual change in USD ( based on the currency rates of each period) and the difference between those is what produces a lower or higher % change than the simple Net Change %.
In order to accomplish this in Excel however, I have this cumbersome file that has a tab for each Country where this operational change dolloar calculaton is happening.
There are two tabs on the Excel Sheet. Totals by Country (and a few other breakouts such as the company level). One is for USD values and the other is Raw Currency. Today these two tabs are generated in Access from the extract I pull from our T&E system.
I figured it would require a bunch of individual reports that go through stages and eventually get sourced together.
I appreciated any more suggestions on this. I will keep developing on my own.
Regards,
Adam E
Currently, the Excel component is used to take the totals by Raw Currency and USD for each country and calculate the amount of operational change dolloars associated with the spend from the old period to the new period (i.e March 2010 vs March 2011 and YTD 2010 and YTD 2011). This Operational change dollar is compared to the actual change in USD ( based on the currency rates of each period) and the difference between those is what produces a lower or higher % change than the simple Net Change %.
In order to accomplish this in Excel however, I have this cumbersome file that has a tab for each Country where this operational change dolloar calculaton is happening.
There are two tabs on the Excel Sheet. Totals by Country (and a few other breakouts such as the company level). One is for USD values and the other is Raw Currency. Today these two tabs are generated in Access from the extract I pull from our T&E system.
I figured it would require a bunch of individual reports that go through stages and eventually get sourced together.
I appreciated any more suggestions on this. I will keep developing on my own.
Regards,
Adam E
Remember that we are essentially "Flying Blind" here.
With no examples of the Excel file or the Access data, it is hard to visualize what you have, what you don't have and what you need.
In a nutshell, it appears that you will have to combine the multiple Country tabs in Excel into one Access table.
But again, without knowing how this data is gathered in Excel (or even what it looks like) it is difficult to determine how best to go about this.
Once a system is devised to combine an normalize the data into an Access table, the combined data is in Access in a "Normalized" manor you can then proceed with the suggestions made by harfang.
So as you can see, some examples of the Excel data are needed, along with the details of how this data is collected.
JeffCoachman
With no examples of the Excel file or the Access data, it is hard to visualize what you have, what you don't have and what you need.
In a nutshell, it appears that you will have to combine the multiple Country tabs in Excel into one Access table.
But again, without knowing how this data is gathered in Excel (or even what it looks like) it is difficult to determine how best to go about this.
Once a system is devised to combine an normalize the data into an Access table, the combined data is in Access in a "Normalized" manor you can then proceed with the suggestions made by harfang.
So as you can see, some examples of the Excel data are needed, along with the details of how this data is collected.
JeffCoachman
ASKER
I understand that more information is needed.
attached is a sample of the most recent excel sheet. I have removed identifiable information for obviously reaons. with summary level sheets (Global, NA, EU, etc.) and the underlying country tabs.
The RAW and USD values are sourced from tabs with the same names (ALL RAW and ALL USD) that were generated from Acces. There are currency values (C2 and D2) for the month to date total and year to date total that is used as part of the operation change calcuation that is take from the sum of the items on the country level sheet. This would need to be sourced into Access most likely instead of using Excel.
The general gudiance so far has been helpful to at least know this is possible. As I mentioend I will work on getting this together on my own and if I run into any specific problems I will come back with a much more detailed question.
Thanks to all comments so far.
Adam E
march-2010-2011.xlsx
attached is a sample of the most recent excel sheet. I have removed identifiable information for obviously reaons. with summary level sheets (Global, NA, EU, etc.) and the underlying country tabs.
The RAW and USD values are sourced from tabs with the same names (ALL RAW and ALL USD) that were generated from Acces. There are currency values (C2 and D2) for the month to date total and year to date total that is used as part of the operation change calcuation that is take from the sum of the items on the country level sheet. This would need to be sourced into Access most likely instead of using Excel.
The general gudiance so far has been helpful to at least know this is possible. As I mentioend I will work on getting this together on my own and if I run into any specific problems I will come back with a much more detailed question.
Thanks to all comments so far.
Adam E
march-2010-2011.xlsx
The first 5 tabs seem to have a different structure than the rest of the sheets...?
Although not impossible, this is a little beyond the time I have to dedicate.
(Normalize and Combine the sheets and import into Access)
Perhaps another Expert can assist you further.
JeffCoachman
Although not impossible, this is a little beyond the time I have to dedicate.
(Normalize and Combine the sheets and import into Access)
Perhaps another Expert can assist you further.
JeffCoachman
ASKER
Not an issue. I really was not expecting someone to sovle this form me completelly.. just guide me in the right direction. I am a intermediate user of Acess just trying to decide if should stick with my current process or if there was a way to cleanly do this in Access (obviously with extentisve intital time to set it up).
Thanks
Adam E
Thanks
Adam E
OK,
...So where do we stand on a resolution of this Q as of now?
...So where do we stand on a resolution of this Q as of now?
ASKER
this was a good summary of steps I should being taking. I did not expect a full solution from beginning to end.. just some guidance
Hello aehrenwo,
I did take a look at your workbook, and I feel much more confident now with answering “yes”.
The first five sheets are probably the reports you want to rebuild in Access. Apparently, the headings can be reconstructed from the sheet SECTORCOPY, with an additional pivoting field (the sheet's header) being a subgroup of ISO country codes included in the report.
The data is obtained (I used `Evaluate Formula´ to trace some of the values) from the country sheets. But these sheets are themselves only a vast collection of queries based on the sheets ALL_RAW and ALL_USD. This was probably a lot of work, and not very rewarding because it's so repetitive.
In Access, you would only use the raw information: list of sectors, list of countries, raw numbers. The same single report could recreate each country sheet (even if new countries are added), and the main reports would not even use the country sheets, but compute the totals directly from the raw numbers.
An experienced Access developer can create these reports in a few hours. As proof on concept, please find a tiny database with the table ALL_USD and a single query creating all the reports of the workbook, depending on the query parameter, but all in USD (I didn't import the table ALL_RAW).
Cheers!
(°v°)
Q-27044151.mdb
I did take a look at your workbook, and I feel much more confident now with answering “yes”.
The first five sheets are probably the reports you want to rebuild in Access. Apparently, the headings can be reconstructed from the sheet SECTORCOPY, with an additional pivoting field (the sheet's header) being a subgroup of ISO country codes included in the report.
The data is obtained (I used `Evaluate Formula´ to trace some of the values) from the country sheets. But these sheets are themselves only a vast collection of queries based on the sheets ALL_RAW and ALL_USD. This was probably a lot of work, and not very rewarding because it's so repetitive.
In Access, you would only use the raw information: list of sectors, list of countries, raw numbers. The same single report could recreate each country sheet (even if new countries are added), and the main reports would not even use the country sheets, but compute the totals directly from the raw numbers.
An experienced Access developer can create these reports in a few hours. As proof on concept, please find a tiny database with the table ALL_USD and a single query creating all the reports of the workbook, depending on the query parameter, but all in USD (I didn't import the table ALL_RAW).
Cheers!
(°v°)
Q-27044151.mdb
So what is your direct question here...?
I have____...and I want_____