Handling currency symbols and +/- in Imports?

[itm60]

I want to import a CSV file containing investment data on a regular basis.The monetary values in the data are usually prefixed by a currency symbol (usually £, occasionally $), and sometimes the values are negative (e.g. -£106.21).
Is there any way that to create a Saved Import which automatically recognises the currency symbols and +/- prefix, or would I need to write a module to process the whole import?
Here's a typical example of what the data might look like:

Symbol,Name,Qty,Price,Day Gain/Loss,Day Gain/Loss %,Market Value £,Market Value,Book Cost,Gain/Loss,Gain/Loss %,Average Price
APAX,Apax Global Alpha Ord,3430,163.60p,£-13.72,-0.24%,"£5,611.48","£5,611.48","£6,498.47",£-886.99,-13.65%,189.4598p
COIN,Coinbase Global Inc Ordinary Shares - Class A,11,$316.91,$28.82,0.83%,"£2,623.14","$3,486.01","$2,901.48",$584.53,20.15%,$263.770909
NCYF,CQS New City High Yield Ord,16982,50.60p,£67.93,0.79%,"£8,592.89","£8,592.89","£8,699.13",£-106.24,-1.22%,51.2256p
CTPE,CT Private Equity Trust Ord,618,493.00p,£-4.49,-0.15%,"£3,046.74","£3,046.74","£2,697.89",£348.85,12.93%,436.5518p
UKW,Greencoat UK Wind,2493,117.90p,£0.00,0.00%,"£2,939.25","£2,939.25","£3,867.79",£-928.54,-24.01%,155.146p
HGT,HgCapital Trust Ord,2459,510.00p,£49.18,0.39%,"£12,540.90","£12,540.90","£12,024.55",£516.35,4.29%,489.0016p
JGGI,JPMorgan Global Growth & Income Ord,317,564.00p,£6.34,0.36%,"£1,787.88","£1,787.88","£1,696.18",£91.70,5.41%,535.0726p
LWDB,Law Debenture Corporation Ord,604,997.00p,£-32.83,-0.54%,"£6,021.88","£6,021.88","£3,994.87","£2,027.01",50.74%,661.4023p
LGEN,Legal & General Group,6347,259.30p,£336.39,2.09%,"£16,457.77","£16,457.77","£14,997.82","£1,459.95",9.73%,236.2978p
NESF,NextEnergy Solar Ord,31189,75.60p,£441.64,1.90%,"£23,578.88","£23,578.88","£29,780.65","£-6,201.77",-20.82%,95.4845p
SHIP,Tufton Assets Ord,15753,$1.11,$315.06,1.79%,"£13,157.68","$17,485.83","£16,745.31","£-3,587.63",-21.42%,£1.062992
VHVG,Vanguard FTSE Dev World ETF USD Acc GBP,19,£91.88,£17.77,1.03%,"£1,745.72","£1,745.72","£1,636.55",£109.17,6.67%,£86.134211
"",,,Totals,,,,,,,,
"",,,GBP,"£1,105.29",0.54%,"£95,481.07","£95,481.07","£102,639.21","£-7,158.14",-6.97%,
"",,,USD,$28.82,0.71%,"£2,623.14","$3,486.01","$2,901.48",$584.53,20.15%,

[Welshgasman]

I think you will need a module.
1. To identify what currency
2. To remove currency symbol(s)

tt= replace("-£106.21","£","")
? tt
-106.21

Also show the actual CSV file and not what it looks like in Excel.

Why is USD totals only 28.82? and not 343.88?

[itm60]

I think you will need a module.
1. To identify what currency
2. To remove currency symbol(s)

tt= replace("-£106.21","£","")
? tt
-106.21

Also show the actual CSV file and not what it looks like in Excel.

Why is USD totals only 28.82? and not 343.88?

No idea why the USD total is 28.82 - that's what was in the download.

I've updated the OP to show the CSV data

[Welshgasman]

That is the trouble with Excel.
Get the formula incorrect and nobody notices.

I believe you need to store the currency with the stock. Then you know what symbol to apply.
That way you can just deal with numbers.
As shown, you can remove the symbols to get just the numeric value.

For a laugh, just import as is, and see what you get.

Might even be worth opening it in Excel and importing from there?
That should then ignore the format symbols?

? activeworkbook.sheets("sheet1").Range("C7").value
 1350

C7 is formatted as $1350
Can you just get a copy of the Excel file instead?

[CJ_London]

I copied your text to create a file - seems to not like the first field header (Symbol)

Then used this sql

SELECT txt.Symbol AS Symbol, txt.Name, txt.Qty, Val([Price]) AS Expr1, Val(Replace(Replace([Day Gain/Loss],"£",""),"$","")) AS GainLoss, Val(Replace([Day Gain/Loss %],"%","")) AS Expr2
FROM [TEXT;DATABASE=C:\Users\chris\Downloads\;HDR=Yes].csvimp.csv  AS txt
WHERE (((txt.Symbol) Is Not Null));

Easily converted to an append query - presume you have a date somewhere so you create a daily list of movements - and change path and file name to suit.

to generate this data (not complete, need to add the other columns if required).



Agree with the other comments - store the currency against the stock record which would simplify the conversion

[Welshgasman]

What are all those?

SELECT txt.

[CJ_London]

Don't know - I just copy and pasted the data provided into a text file - also edited the file to delete any hidden characters, but to no effect. I suspect if the OP opens the original file, they won't be there.