Paypal exchange rate query problem

English
, , ,
#1

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
image description1366×768 213 KB

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

#2

Karma increased. Just edit your question & add info.

#3

I was the “somebody”. The sample given doesn’t help in what is to be done. The first point of mass confusion is this PayPal table. There is nothing (at least apparent) on what to use and when. One glaring point here occurs on 16/08/17. Here there are 5 currency conversion records. 3 for EUR and 2 for USD with 2 of the EUR’s being very close in time. Programs and electronic procedures are based upon specifics. What is the determination here? And what do these relate to?

#4

Hello @pascale,

Just a thought, You wrote:
“I want to create a query […], which calculates the exchange rate for a given currency and records this for each payment above.”

I thought the exchange rate is fixed at the time of transaction (?)
In that case you could just add another field in your table, which stores the historical exchange rate used for that particular currency at the time of that particular transaction.

#5

Hi, I suppose when you create something yourself it all looks pretty obvious! in the paypal table columns “A to P” are simple data, each row is a transaction there can be several transaction each day. Column “Q, R and S” are a running balance for each currency, columns “T and U” are the exchange rate values for “USD and GBP” against the Euro column V is the running total of all the currencies as Euros and column W is the exchange rate for each transaction with regard to the Dévise (continues)

#6

(continuation) I want to some how replicate this information automatically in Base via queries but I’ am beginning to think this is impossible as running totals are impossible see this Problem in Per Row Computation (View topic) • Apache OpenOffice Community Forum, hi @librebel, sorry but the exchange rate is set when i exchange USD into EURO so I have to wait for the best rate. It is late now here will come back tomorrow many thanks to @ratslinger and @librebel

#7

You statements don’t seem to match the illustration. You say 'Column “Q, R and S” are a running balance for each currency" but my understanding of a ‘running balance’ is a result of current plus previous. Don’t see it in these columns. Cols ‘T’ & ‘U’ - how did you get those. This back to my previous unanswered questions. The first trans in Calc are dated 14/08/17 but the PayPal table in the .odb has no conversion factors for that date. Again how would you know what to use? More…

#8

Col ‘V’ running total? Again don’t see it. Col ‘W’ still unclear to meaning. “Impossible” is a strong word to use. Haven’t seen anything here yet which that would apply to. Your approach may be entirely wrong for the end result but I can’t determine that because of the questions. Still seeing some human intervention/judgement occurring. You need to have a specific procedure.

#9

You could make a running total using the SUM function in your SQL query, e.g.

SELECT SUM( "Net" ) FROM "PayPal" WHERE "Date" = {d '2017-07-31' } AND "Type" = 'Conversion de devise initiée par l''utilisateur'

Please note the extra apostrophe in l’'utilisateur, else it results in an error…
Also the format of the date string is very specific here.

#10

Hi @ratslinger, I have added a copy of paypal table with 3 columns removed as they held personal info. the column you do not understand “v” is now “T” this column shows the exchange rate for each transaction, it is used in the main table “vt” sold items along with Ebay and stock to allow me to calculate the value of each sale in Euros. (sorry that the column letters have change) please refer to the table that you can download. “N, O and P” are running totals in the relevent currency, more

#11

continues; “Q & P” are currency rates ie 0.87€ to 1 dollar, 1.13€ to 1 GBP. column “S” is a running total of the balance in € at that moment. this all sounds complicated but in France the accounting system is complicated. I want to be able to see and use the same information using Base. I am flexible, I need the following columns of data; Date: Type: Dévise: AvComm: comm: net: and NDT, and then I hope to use queries to find the balance at a set time and the exchange rate transaction. please help

#12

Dear @librebel thank you so much for your hard work, I have spent the day searching for treasures around Bordeaux garage sales, so I will try your SQL query tomorrow.kind regards Paul

#13

Hi, This is not a full answer but I Have advanced! I have changed my “paypal” table to include an “ID” column as joint primary key along with “NDT” I have also added an exchange rate column, this is calculated in the Calc document and then imported as data, I have deleted “time” column as this is no longer needed. This has now enabled me to create three queries, one for each currency where I have added a column ‘Net’ * ‘exchangeRate’ This gives me the value of each transaction in Euros, These work perfectly, so as @ratslinger said “impossible” is a strong word!! I am making now working on a way to combine all the totals to give me a value for the “paypal” account in Euros, I would like to somehow use a form where I enter the date and in three other columns I have the sum of all transaction up to that date, this is very important to me as it enables me to control my calculations,
I have advanced but there is more work to do!

@librebel wrote; You could make a running total using the SUM function in your SQL query, e.g.
SELECT SUM( “Net” ) FROM “PayPal” WHERE “Date” = {d ‘2017-07-31’ } AND “Type” = ‘Conversion de devise initiée par l’‘utilisateur’ so i am going to try to use this in my form

Again many thanks to all who have taken an interest in my Question

#14

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.