vhmetals

Hi,

Thanks for the suggestions. I appreciate your time.

Tobias,

Thank you for your prompt reply. Your suggestion regarding the use of CSV format resolved the rounding issues for both tables. Each value now has a thousandths separator, is rounded to two decimal places, including .00. It’s a great temporary fix.

Now if only I can find a way to automatically update the price of each respective metal without daily manual imports. Even if I upload a new file daily, the price is not accurate throughout the day. The metals are very volatile. So somehow I need a viable solution. I wonder how sites like Kitco are able to constantly update their change in price.

Finally, can you tell me how to change the color of specific lines in the table so that the presentation of the information is more user friendly?

Thanks in advance!

https://www.vonharringa.com/buy-price
https://www.vonharringa.com/fair-market-value

Tobias,

I have two issues that I need help with:

(1) Even though each cell has the round function (created in Excel), when I import, only some of the cells round to two decimal places while others do not. Do you have a custom code that forces each cell to round to two decimal places? I cannot use the CSV solution since my excel spreadsheet has multiple pages of input values that are not displayed but are cell referenced.

(2) My excel spreadsheet automatically downloads the latest prices for precious metals from Kitco. The feature is lost when I import my file. Do you have a solution for this problem?

Any help would be great. Thank you for all of your time and hard work!

https://www.vonharringa.com/fair-market-value
https://www.vonharringa.com/buy-price

I must have misunderstood. Thanks for that tip. I’m sure I will use it in the future. On a side note, thanks so much for the frequently asked questions section. I was able to change CSS colors quite easily.

silver melt value chart

Tobias,

Finally, finished my project. Thanks for all of your help! I was having issues in open office after I copied over the formula from https://writecodeonline.com/php/. I was getting the following error message:

Err: 508

I had to manually enter = for my entries. Open office would give an error message if = was copied into the cell. The cells had to begin with round. If they begin with = then there is an error 508.

I tried special pasting. But it just calculated the value without keeping the formula.

I followed your instructions. Very smart idea. I finally have the code exactly right for each cell except for:

1101 – 2427 need = in front of the code. I tried the find and replace but it won’t allow me to enter =. It just shows up as an error. But your method still saves me quite a bit of time. All i have to do is enter = for each entry instead of numbering each one. Thanks for the help!

Thank you for the tip. I will try this.

Thanks for the suggestion. It almost worked but not quite. https://writecodeonline.com/php/ is a very powerful website. Thanks for that link. I used the following code for a small batch:

for ( $i = 2605; $i <= 2779; $i++ ) {
echo ‘=round(C’ . $i . ‘*0.925*0.90*E2,0)’.””;
}

Then I pasted the results into open office spreadsheet for C2605 – C2779. I used the “paste special” function for unformatted text. Used Western Apple separated by a comma.

After pasting, it yielded the results of the formula instead of the actual formula. So it performed the mathematical computation. But I need the actual formula as text instead of a computation.

So it almost worked. Thanks again for the suggestion. Looks like I will have to input manually.

[signature moderated]

I was able to autofill mathematical formula in open office. So Column D has:

=round(C300*0.925*0.90*E2,0)

I did this to eliminate “copy and paste” function for 2800+ entries. But now I need to manually change :

C300
C301
C302
C303
C304

…and so on up to C2864

Is there a way to do this instantly?

[signature moderated – please don’t add unnecessary links to your site]

I exported my table in .csv format to open office. Used “find and replace” successfully to remove “ozt” from 2800 entries. Thank you so much for saving me time with this issue. I gave you 5 stars and left a review.

Did you have any ideas on how to bulk edit a column. I need to add the following expression to Column D for 2800 entries for silver melt value chart:

=round(C2*0.925*0.90*E2,0)
=round(C3*0.925*0.90*E2,0)
=round(C4*0.925*0.90*E2,0)
=round(C5*0.925*0.90*E2,0)

…and so on up to C2800

Any suggestions are greatly appreciated! If not, it’s going to be a long day.

Thanks for the link. I will leave a review soon. My table data originated on TablePress. Should I be saving a hard copy or is my data secure on Table Press?

One other question if you don’t mind. Is there a way to input the formula in a bulk fashion? For example,

=round(C2*0.925*0.90*E2,0)
=round(C3*0.925*0.90*E2,0)
=round(C4*0.925*0.90*E2,0)
=round(C5*0.925*0.90*E2,0)

…All the way until C2800

I ended up using 0 so that the value would round to the nearest dollar. Looks more professional.

Where can I leave a review?

Thanks I will work on that. Appreciate all the help!

That solved the issue. When it rounds, though, it leaves off the zero. It should read:

1971.40

Instead it reads,

1971.4

Also, is there a way to add a comma so that it reads,

1,971.40

Is there a shortcut to remove the phrase “ozt” from column C in bulk. I have 2800 entries so it would take a long time to manually remove that phrase.