Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Paypal exchange rate query problem

Hi again! I am back with the same question but with more information about what I have and What I need, I am looking for pointers to help me solve this (ie tutorials forums etc) I have a table called PayPal which I down load as a csv file and then copy into the db table, I live in france so my Home currency is EUR, i sell in EUR, USD and GBP. For my accounts I need to know the value of each transaction in EUR, the value of which is decided when I exchange the currencies two times a week, this is always after I have been paid. With calc this was easy as i had a table and in one column for each currency I had this formula
=IF($I1155="USD",IF(OR(G1155="Conversion de devise initiée par l'utilisateur"),$L1156/$L1155*-1,$T1156+0),T1156+0)

At the end of the table I had an approximate value for the exchange rate because the formula looked at the result of the cell below. It worked very well but this and other formula in the document made it very heavy and slow.

Date | Heure | Type | Devise |Avant commission

27/07/17|21.45 |payment | USD | 275.99

27/07/17|22.45 |payment | USD | 285.99

28/07/17|10.03 |conversion | USD | -536.25

28/07/17|10.03 |conversion | EUR | 410.25

30/0/7/17|08.00|Bank trans| EUR | -400.00

I want to create a query that I can use later, which calculates the exchange rate for a given currency and records this for each payment above. I expect to use one query for each currency then a further query to bring all the information together. At the moment I have set "Date" "Time" and "Transaction number" as primary keys, This can be changed and a unique ID column added if needed. When I have enough points I will add a screen shot of the calc table and a copy of the db to Help. Thanks in advance for anybody who takes a look Regards Paul.

Paypal exchange rate query problem

Hi again! I am back with the same question but with more information about what I have and What I need, I am looking for pointers to help me solve this (ie tutorials forums etc) I have a table called PayPal which I down load as a csv file and then copy into the db table, I live in france so my Home currency is EUR, i sell in EUR, USD and GBP. For my accounts I need to know the value of each transaction in EUR, the value of which is decided when I exchange the currencies two times a week, this is always after I have been paid. With calc this was easy as i had a table and in one column for each currency I had this formula
=IF($I1155="USD",IF(OR(G1155="Conversion de devise initiée par l'utilisateur"),$L1156/$L1155*-1,$T1156+0),T1156+0)

At the end of the table I had an approximate value for the exchange rate because the formula looked at the result of the cell below. It worked very well but this and other formula in the document made it very heavy and slow.

Date | Heure | Type | Devise |Avant commission

27/07/17|21.45 |payment | USD | 275.99

27/07/17|22.45 |payment | USD | 285.99

28/07/17|10.03 |conversion | USD | -536.25

28/07/17|10.03 |conversion | EUR | 410.25

30/0/7/17|08.00|Bank trans| EUR | -400.00

I want to create a query that I can use later, which calculates the exchange rate for a given currency and records this for each payment above. I expect to use one query for each currency then a further query to bring all the information together. At the moment I have set "Date" "Time" and "Transaction number" as primary keys, This can be changed and a unique ID column added if needed. When I have enough points I will add a screen shot of the calc table and a copy of the db to Help. Thanks in advance for anybody who takes a look Regards Paul.Paul. Sombody has given me extra Karma!!!! OOOk below is a screen shoot of the calc paypal table and a copy of the db I am trying to create for my accounts BDF.odb image description

Paypal exchange rate query problem

Hi again! I am back with the same question but with more information about what I have and What I need, I am looking for pointers to help me solve this (ie tutorials forums etc) I have a table called PayPal which I down load as a csv file and then copy into the db table, I live in france so my Home currency is EUR, i sell in EUR, USD and GBP. For my accounts I need to know the value of each transaction in EUR, the value of which is decided when I exchange the currencies two times a week, this is always after I have been paid. With calc this was easy as i had a table and in one column for each currency I had this formula
=IF($I1155="USD",IF(OR(G1155="Conversion de devise initiée par l'utilisateur"),$L1156/$L1155*-1,$T1156+0),T1156+0)

At the end of the table I had an approximate value for the exchange rate because the formula looked at the result of the cell below. It worked very well but this and other formula in the document made it very heavy and slow.

Date | Heure | Type | Devise |Avant commission

27/07/17|21.45 |payment | USD | 275.99

27/07/17|22.45 |payment | USD | 285.99

28/07/17|10.03 |conversion | USD | -536.25

28/07/17|10.03 |conversion | EUR | 410.25

30/0/7/17|08.00|Bank trans| EUR | -400.00

I want to create a query that I can use later, which calculates the exchange rate for a given currency and records this for each payment above. I expect to use one query for each currency then a further query to bring all the information together. At the moment I have set "Date" "Time" and "Transaction number" as primary keys, This can be changed and a unique ID column added if needed. When I have enough points I will add a screen shot of the calc table and a copy of the db to Help. Thanks in advance for anybody who takes a look Regards Paul. Sombody has given me extra Karma!!!! OOOk below is a screen shoot of the calc paypal table and a copy of the db I am trying to create for my accounts BDF.odb image description

As you can see from the table I have a running balance column for each currency and a single balance column for all transactions in EUR this for me is a complicated table and slows everything down so I want to store the data in a table and then use queries and reports to see the results, hopefully making it quicker and easier to add and edit the data, At the moment I am searching the net on "how to have a running balance column"? Regards paul