Creating a formula based on TODAY’s day

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!

All together, you're looking at the following formula for B3 in your example:

=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")

edit

I temporarily forgot about filter, which makes this task much easier: =COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")

If I understand your questions correctly, you want to be able to change the value in F2 but keep a history of that value in the 'C` column. All this, while all other cells update automatically by dragging down the cells.
In that case, I don't know a possible way.

What you could do is to not get the C column value from the "Current Gold Price" value in the F2 cell, but perhaps have it pulled directly from the source. It depends on how your source is. If that is a column that gets updated based on the date then use it as a reference just as you are doing with F2.